Using CHARINDEX and SUBSTRING in a CASE statement

Question

I need to evaluate a field with a CASE statement. The field name is Commodity and is a varchar(255). The values differ and I need to extract a specific portion from it after a specific character. The character is a '>'. I was able to come up with the value I want returned after the > by using the following code:

SUBSTRING(Commodity, CHARINDEX('>', Commodity) + 2, LEN(Commodity))

I am however unsure of how to work this into my CASE statement. I need to test for Is Null and then just assign it a value of 'No Commodity'. Then I need to test for the presence of a > and then implement the code above to return the value. Then I need to test for when there is no > but it is not null and just return the value of the Commodity field.

Answer 1

You just need to have these three conditions in when clauses. You can use charindex to make sure the > character exists in the string:

CASE 
WHEN commodity IS NULL THEN 'No Comodity'
WHEN CHARINDEX('>', Commodity) > 0 THEN 
     SUBSTRING(commodity, CHARINDEX('>', commodity) + 2, LEN(commodity))
ELSE comodity
END