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

Answers


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

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.


It should be like below

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

Need Your Help

How to display modified date time with 'find' command?

linux command-line grep find stat

With a find command, I can display directories names with multiple levels. The following command display all directories under /var path with a depth of 2: