Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
select case 'NULL' when 'NULL' then 'f' end as out

output: f

but

select case (select  (SELECT LEFT(l.list,LEN(l.list)-1) 
    FROM
    (SELECT  o.Reciting_Path + '|' + o.Declaimer +',' AS [text()]
    FROM    Poetry_Reciting  AS o
    where   o.Poetry_Id=6764
    FOR XML PATH(''))l(list)) )  when  'NULL' then 'N' end as dd

should just return NULL and NULL is correct but the case statement does not work.

I want 'N' in output

share|improve this question

migrated from meta.dba.stackexchange.com Jan 3 at 0:59

This question came from our discussion, support, and feature requests site for database professionals who wish to improve their database skills and learn from others in the community.

3  
How could the output be 1? Shouldn't it be f? Also what is the point of performing all of that concatenation if you're never going to use the output? Wouldn't just checking for the existence of those values be sufficient? –  Aaron Bertrand Jan 3 at 1:40

1 Answer 1

up vote 8 down vote accepted

There are two points of confusion here.

The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value.

The second is that you can use NULL in equality comparisons; and you cannot. Try this:

SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END;

Result:

----
0

The reason is that NULL is unknown - so SQL Server can't tell you whether it is or is not equal to a specific value (or even to another NULL value). To check if a value is NULL, you can't say CASE WHEN value = NULL or CASE value WHEN NULL, but you can say CASE WHEN value IS NULL:

SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END;

Result:

----
1

Now, I think there are better ways to accomplish what you seem to need (I'm not confident I understand the requirements here at all), but in the immediate term you can correct this by saying:

select case WHEN (select ... )  IS NULL then 'N' end as dd;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.