sql ? - 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)

sql ?

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-03-17 : 16:27:39

I want to identify two different situation of the id:
(1) if ID=9digit and a follow character (i.e 100200300F), then ID1
(2) if ID=9characters with follow a space then ID2 (i.e 200300400 )
Note: datatype=varchar(10)
How do I write sql statement to identify that. Thanks,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 16:31:40

SELECT CASE
WHEN ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' THEN ID1
WHEN ID LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] ' THEN ID2
END
FROM myTable

ntn104
Posting Yak Master

175 Posts

Posted - 2011-03-17 : 16:42:08

quote:
Originally posted by robvolk

SELECT CASE
WHEN ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' THEN ID1
WHEN ID LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] ' THEN ID2
END
FROM myTable

Thanks, I tried this
SELECT CASE
WHEN ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' THEN ID1
ELSE ID2
END AS IDTYPE
FROM MYTABLE

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-18 : 03:41:42

quote:
Originally posted by ntn104

quote:
Originally posted by robvolk

SELECT CASE
WHEN ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' THEN ID1
WHEN ID LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] ' THEN ID2
END
FROM myTable

Thanks, I tried this
SELECT CASE
WHEN ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' THEN ID1
ELSE ID2
END AS IDTYPE
FROM MYTABLE

Thanks,

Note that if the last character is other than a character, then ID2 will be selected

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources