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.

Microsoft SQL Server 2008SQL

Last Comment

Simon

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Simon

12/19/2014

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Jim Horn

12/19/2014

CASE statements support multiple WHEN .. THEN statements, where SQL executions them sequentially until one WHEN expression evaluates to True, then returns what's in the THEN.

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

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.

Vitor Montalvão

12/19/2014

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

Open in new window

Vitor Montalvão

12/19/2014

Damn. I knew I should refresh the page before clicking in the Submit button!

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Simon

12/19/2014

Also underscore is a wildcard character. You should use [_] to represent the literal.

Sanjay

12/19/2014

ASKER

Thank you everyone. Much appreciated.

Jim Horn

12/19/2014

<Frazier Crane> When you know the answer it's hard not to hit the button..

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Simon

12/19/2014

Final version with [ and ] around the underscore (otherwise LIKE treats it as a wildcard representing any single character):

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

Open in new window

MSDN reference for the LIKE operator