case when null not working as expected

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

You want to put something like this:

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

case might not work with null. Use coalesce or isnull.

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

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

It should be like below

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

declare @t table(data varchar(10) null)
insert into @t values('something')
insert into @t values(null)
select data,     
case  when data  is null     
 then 'missing'        else 'not missing' 
  end as test from @t

This will give the expected answer.

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