Solved

sql script help in using case statment with a select statement

Posted on 2014-12-19
8
214 Views
Last Modified: 2014-12-19
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.
0
Comment
Question by:sxxgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40509068
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)) 
             when @FullName like '%_%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName)) end AS Rev

Open in new window


the syntax for each case is just to separate with ' when ', rather than 'end as Rev,'

For sanity, you should probably include an 'else' case as well.
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
'n/a' 
end AS Rev

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40509070
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.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40509075
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

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40509082
Damn. I knew I should refresh the page before clicking in the Submit button!
0
 
LVL 18

Expert Comment

by:Simon
ID: 40509085
Also underscore is a wildcard character. You should use [_] to represent the literal.
0
 

Author Closing Comment

by:sxxgupta
ID: 40509087
Thank you everyone.  Much appreciated.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40509088
<Frazier Crane>  When you know the answer it's hard not to hit the button..
0
 
LVL 18

Expert Comment

by:Simon
ID: 40509094
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
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question