stewartc-708166 (10/10/2011)

---

This is a good question, showing that care needs to be exercised when working with NULLs


As shown in the explanation, the way the CASE is structured will need to be changed somewhat, i.e.

SELECT CASE WHEN COL1 IS NULL THEN 'Is Null'  
 WHEN COL1 = '0' THEN 'Is Zero'  
 WHEN COL1 = '1' THEN 'Is One' 
 WHEN COL1 = '2' THEN 'Is Two' 
 END 
FROM 
( 
 SELECT NULL AS Col1 
 UNION 
 SELECT '0' AS Col1 
 UNION 
 SELECT '1' AS Col1 
 UNION 
 SELECT '2' AS Col1 
) TMP

sqlzealot-81 (10/10/2011)

---

I guess, you can achieve by the below way,

SELECT 
CASE IsNull(COL1,'') WHEN '' THEN 'Is Null' 
 WHEN '0' THEN 'Is Zero' 
 WHEN '1' THEN 'Is One'
 WHEN '2' THEN 'Is Two'
 END
FROM
(
 SELECT NULL AS Col1
 UNION
 SELECT '0' AS Col1
 UNION
 SELECT '1' AS Col1
 UNION
 SELECT '2' AS Col1
) TMP

Sean Lange (10/10/2011)[hrThis works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.