Sanjay
asked on
sql script help in using case statment with a select statement
i am new to sql scripting. Basically all I am trying to do is to output one column called 'Rev' based on the two case statements below. The scripting works for the most part. The only problem is that it outputs two columns called 'Rev'. i just need one column. So if the first case statement condition is not found, then move to the second case condition etc.
DECLARE @FullName VARCHAR(100)
SET @FullName = '07000496_C.F2'
SELECT
case when @FullName like '%_%' and @FullName not like '%.%' then SUBSTRING(@FullName, CHARINDEX('_', @FullName) + 1, LEN(@FullName)) end AS Rev,
case when @FullName like '%_%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName)) end AS Rev
Thank you.
DECLARE @FullName VARCHAR(100)
SET @FullName = '07000496_C.F2'
SELECT
case when @FullName like '%_%' and @FullName not like '%.%' then SUBSTRING(@FullName, CHARINDEX('_', @FullName) + 1, LEN(@FullName)) end AS Rev,
case when @FullName like '%_%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName)) end AS Rev
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You just need a CASE statement:
SELECT case
when @FullName like '%_%' and @FullName not like '%.%' then SUBSTRING(@FullName, CHARINDEX('_', @FullName) + 1, LEN(@FullName))
when @FullName like '%_%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName))
end AS Rev
Damn. I knew I should refresh the page before clicking in the Submit button!
Also underscore is a wildcard character. You should use [_] to represent the literal.
ASKER
Thank you everyone. Much appreciated.
<Frazier Crane> When you know the answer it's hard not to hit the button..
Final version with [ and ] around the underscore (otherwise LIKE treats it as a wildcard representing any single character):
MSDN reference for the LIKE operator
SELECT
case when @FullName like '%[_]%' and @FullName not like '%.%' then SUBSTRING(@FullName, CHARINDEX('_', @FullName) + 1, LEN(@FullName))
when @FullName like '%[_]%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName))
else -- if you omit this you would get NULL by default for unmatched cases
'n/a'
end AS Rev
MSDN reference for the LIKE operator
Open in new window
I have an article out there called SQL Server CASE Statments that illustrates multiple WHEN..THEN, nested statements, and many other features of CASE statements. If it helps please click the big green 'Was this article helpful?' button at the end of the article.