CODEWITHSUNDEEP
sqlsql-server-2008
weewa
weewa

Reputation: 125

SQL I need multiple cases for one column

My previous question was - insert substring into new column

I have two cases I want to run on the same column.

The first - 

SET [ref_id] = CASE 
WHEN CHARINDEX('-', [full_id]) = 4 
THEN SUBSTRING([full_id], 1, 3) ELSE NULL

The second - 

SET [ref_id] = CASE 
WHEN CHARINDEX('ZZZ-', [full_id]) = 1 
THEN SUBSTRING([full_id], 5, 3) ELSE NULL

My [full_id] column data format is mainly ABC-123D2-45FG67 BUT some of it is all numbers or is prefixed with ZZZ-

If the data is only numbers I want the [ref_id] column to be NULL.

If the data is prefixed with ZZZ- I want that part to be skipped.

If the data is not ZZZ- and starts with ABC- I want to copy the ABC (in some cases there is AB-1234 and that should be NULL).

My code works fine except that the two strings do not function together and when I run them separately they cancel out the work of the previous query.

How can I run both and end up with NULLS only for the data in formats like 123456 and AB-1234

Thank you for the help.

Upvotes: 0

Views: 14661

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You want to use a single case statement for this:

SET [ref_id] = CASE WHEN CHARINDEX('ZZZ-', [full_id]) = 1 
                    THEN SUBSTRING([full_id], 5, 3)
                    WHEN CHARINDEX('-', [full_id]) = 4 
                    THEN SUBSTRING([full_id], 1, 3)
                    ELSE NULL
               END

Upvotes: 3

Vinny Roe
Vinny Roe

Reputation: 901

Isn't this just a case (hah!) of nesting your case statements?

SET [ref_id] = CASE WHEN CHARINDEX('ZZZ-', [full_id]) = 1 THEN SUBSTRING([full_id], 5, 3) ELSE 
  (CASE WHEN CHARINDEX('-', [full_id]) = 4 THEN SUBSTRING([full_id], 1, 3) ELSE NULL end ) end

Edit: Removed "else null" towards end. Think that's better. I don't have a server available to me to test.

Upvotes: 0

Related Questions