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
Solved

sql script help in using case statment with a select statement

Posted on 2014-12-19
8
205 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
  • 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 65

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 48

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 48

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 65

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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