CASE and IS NULL - graci

I need to convert the nulls in a column to a value.

When I run the following:

SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'

END AS items
FROM INV_ITEMS

It keeps returning the NULL values as NULL instead of converting them to
'UNK'.
What am I doing wrong?

CASE and IS NULL - Warren Brunk

Are you sure its really a null value and not just blank or something

SELECT
CASE
WHEN  COL1 IS NULL THEN 'UNK'
When  COL1 = 'NULL' THEN 'UNK'
When Col1 = '' then 'UNK'
WHEN  COL1 = 'SUGAR' THEN 'SGR'
WHEN  COL1 = 'SALT' THEN 'SAL'
WHEN  COL1 = 'PEPPER' THEN 'PEP'

END AS items
FROM esi_store_orderheader

Make sure you have all your bases covered and that the null values really
are null.

--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/

CASE and IS NULL - Bruce

SELECT isnull(

CASE

COL1

WHEN 'SUGAR' THEN 'SGR'

WHEN 'SALT' THEN 'SAL'

WHEN 'PEPPER' THEN 'PEP'


END, 'UNK') AS items

FROM INV_ITEMS

CASE and IS NULL - Tibor Karaszi

... or perhaps the string 'NULL'

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

CASE and IS NULL - SQL Menace

One problem is see is that you don't have an ELSE so if a value is
'POTATO' then you will get a NULL also

WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'N/A' END......

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx