character count in a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

character count in a string

Author

Topic

ssdeveloper
Starting Member

37 Posts

Posted - 2011-09-01 : 15:01:46

Hi Guys,
I need some help with something. I have these dates cutout from a column which is string, so some of them are not completely dates.
For example I have 'me_app_08_06_2010' and I have 'me_app_26'
I am cutting the date part (using isnumeric function) for month day and year and tried to concatenate and read now as 08-06-2010 and then tried to convert to date. While converting to date its kind of stuck at this particular record 'me_app_26' since it cannot convert 26 to date format. So I want to use the logic saying If the hifen '-' part occurs equal or more than twice consider it or else display null.
Is there a possibility we can count the number of the hifens in the string of the column. Any help would be appreciated. Thanks in advance!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 15:11:04

WHERE YourColumn LIKE '%[0-9][0-9][_][0-9][0-9][_][1-2][0-9][0-9][0-9]%'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 15:13:25

Or, if the format is always 'me_app_%' then

WHERE LEN(yourColumn) = 17

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 15:15:05

or

WHERE RIGHT(youColumn, 10) like '[0-9][0-9][_][0-9][0-9][_][1-2][0-9][0-9][0-9]'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-01 : 15:27:33

one more:

Declare @t table (a varchar(32))

insert @t values('me_app_08_06_2010')
insert @t values('me_app_26')

SELECT	Convert(datetime, Replace(Replace(a, 'me_app_', ''), '_', '-'))
FROM	@t
WHERE	a LIKE '%[0-9][0-9][_][0-9][0-9][_][1-2][0-9][0-9][0-9]%'

ssdeveloper
Starting Member

37 Posts

Posted - 2011-09-01 : 15:38:56

Thanks for your responses. I appreciate it, One of the options amused me though keeping other complicated code involved in this whole process already.
So If I want to make those(like three digit or 4 digit entries which cannot be converted to date) entries null, I want to use it this way:
NULLIF(LEN (derived_column_name),<8 )

But its not working. Is '<8' part not acceptable in the 2nd argument of the NULLIF? Thoughts?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-01 : 15:53:27

No it's not valid. However, you can use a CASE expression: CASE WHEN LEN(derived_column_name) < 8 THEN NULL ELSE derived_column_name END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources