SQL Server Performance Forum – Threads Archive

Select Case For Null Value

Hi , Why the query does not work for the NULL value store in the table ? Anyone can help ? select agn_id,
Case Home_Phone
When ‘ ‘ Then ‘NA’
When NULL then ‘NA’
End As Home_Phone
from l_agent Thanks ,
Travis

---

Try:
select agn_id,
Case Home_Phone
When ‘ ‘ Then ‘NA’
When IS NULL then ‘NA’
End As Home_Phone
from l_agent Else better select would be
select agn_id,
Case
When ISNULL(Home_Phone,’ ‘)=’ ‘ Then ‘NA’
ELSE ”
End As Home_Phone
from l_agent

---

That’s because you cannot compare anything with NULL. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Even better select agn_id,
ISNULL(NULLIF(Home_Phone, ‘ ‘), ‘NA’) As Home_Phone
from l_agent
Roji. P. Thomas
http://toponewithties.blogspot.com

---

By the way, CASE <expression> WHEN IS NULL THEN … ELSE … END is invalid syntax. You can use: CASE WHEN <expression> IS NULL THEN … ELSE … END

---

]]>