case when null not working as expected

user_81514 0
Viewed 29270

consider this very short T-SQL code that does a test on a nullable column using a case

declare @t table(data varchar(10) null)
insert into @t values('something')
insert into @t values(null)

select data, 
    case data when null
        then 'missing'
        else 'not missing'
    end as test
from @t

the output that I get is:

data        test
---------   -----------
something   not missing
NULL        not missing

However what I was expecting was

data        test
---------   -----------
something   not missing
NULL        missing

What am I missing concerning the test on this nullable value

sql-server-2008 tsql sql-server
5 Answers

Realise this is an old post, but I came across the thread as I had the same problem recently. I had previously been successful using the syntax (as some have indicated above) as:

case [sourcecolumn] when NULL then ...<alternative output>...

Example:

    , CASE LOWER(EmailAddr)
        WHEN NULL THEN NULL
        WHEN '' THEN NULL
        WHEN 'no email address' THEN NULL -- several mixed case variations
        -- Source email address may also contain carriage returns!
        -- REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
        -- Retain original email address, but switch it to all lower case and remove any whitespaces/CR/LF chars.
        ELSE LOWER(REPLACE(REPLACE(REPLACE(EmailAddr, ' ', ''), CHAR(10), ''), CHAR(13), ''))
    END

But recently this has not given the expected/desired results (not sure if it's due to the source column type, it's just a Varchar).

However, I found the following syntax worked for me instead:

case When [sourcecolumn] IS Null Then ...<alternative output>...

Note that if you have a set of conditions to check for (not just Null), then you have to add the [sourcecolumn] to each conditional test line.

The re-worked example:

    , CASE
        WHEN LOWER(EmailAddr) IS NULL THEN NULL
        WHEN LOWER(EmailAddr) = '' THEN NULL
        WHEN LOWER(EmailAddr) = '#N/A' THEN NULL
        WHEN LOWER(EmailAddr) = 'no email address' THEN NULL -- several mixed case variations
        -- Retain original email address, but switch it to all lower case and remove any possible whitespaces/CR/LF chars.
        ELSE LOWER(REPLACE(REPLACE(REPLACE(EmailAddr, ' ', ''), CHAR(10), ''), CHAR(13), ''))
    END
user_12310080
Related