CASE and IS NULL

[gracie]

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?

[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

[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
*/

"gracie" <gra...@discussions.microsoft.com> wrote in message
news:2D1C0EC2-6A78-4676...@microsoft.com...

[Bruce]

SELECT isnull(

CASE

COL1

WHEN 'SUGAR' THEN 'SGR'

WHEN 'SALT' THEN 'SAL'

WHEN 'PEPPER' THEN 'PEP'

END, 'UNK') AS items

FROM INV_ITEMS

"gracie" <gra...@discussions.microsoft.com> wrote in message
news:2D1C0EC2-6A78-4676...@microsoft.com...

[Tibor Karaszi]

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

... or perhaps the string 'NULL'

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

"Warren Brunk" <wbr...@techintsolutions.com> wrote in message
news:6827D2F0-BCDD-4B84...@microsoft.com...