Solved

sql script help in using case statment with a select statement

Posted on 2014-12-19
8
210 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 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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan not deleting backup files as anticipated 4 30
SQL query 7 49
T-SQL: Only Wanting One Record 8 59
how to resize column length with primary ket 4 32
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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