stewartc-708166 (10/10/2011)

---

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

Ajay.Kedar (10/10/2011)

---

I tried and got wrong in first select case 🙂

But wanted to know that

How can we chack null in this case then?

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