Using CASE statement with isnull and else

Question

I have a column Color which contains Black, Red, NULL, WW, RR.

I want a column which has

if color black then 'B'
if color red then 'r'
if color is Null then 'Empty'
for all other entries 'n/a'

I'm using the following, but it throws an error:

SELECT Name,
        CASE color
            WHEN 'black' THEN 'b'
            WHEN 'red' THEN 'r'
            WHEN ISNULL(color, 'empty')
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

Answer 1

You can use either CASE structure to do this, but you can't call a function if you use the CASE fieldname WHEN approach, so you can either use CASE WHEN fieldname condition:

SELECT Name,
        CASE WHEN color = 'black' THEN 'b'
            WHEN color = 'red' THEN 'r'
            WHEN color IS NULL THEN  'empty'
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

OR:

SELECT Name,
        CASE color
            WHEN 'black' THEN 'b'
            WHEN 'red' THEN 'r'
            WHEN NULL THEN 'empty'
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

Answer 2

You could try this way

  SELECT Name,
          CASE 
              WHEN color = 'black' THEN 'b'
              WHEN color = 'red' THEN 'r'
              WHEN color is null  THEN  'empty'
                ELSE 'n/a'
          END AS Color_code
  FROM SalesLT.Product;