T-SQL CASE Clause: How to specify WHEN NULL

Question

I wrote a T-SQL Statement similar like this (the original one looks different but I want to give an easy example here):

SELECT first_name + CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS Name FROM dbo.person

This Statement does not have any syntax errors but the case-clause always chooses the ELSE-part - also if the last_name is null. But Why?

What I want to do is to unite first_name and last_name, but if last_name is null the whole name becomes null:

SELECT first_name + CASE last_name WHEN null THEN '' ELSE ' '+last_name END AS Name FROM dbo.person

Do you know where the problem is? Thank you for your help!

Answer 1

CASE WHEN last_name IS NULL THEN '' ELSE ' '+last_name

Answer 2

The WHEN part is compared with ==, but you can't really compare with NULL. Try

CASE WHEN last_name is NULL  THEN ... ELSE .. END

instead or COALESCE:

COALESCE(' '+last_name,'')

(' '+last_name is NULL when last_name is NULL, so it should return '' in that case)

Answer 3

Given your query you can also do this:

SELECT first_name + ' ' + ISNULL(last_name, '') AS Name FROM dbo.person

Answer 4

The problem is that null is not considered equal to itself, hence the clause never matches.

You need to check for null explicitly:

SELECT CASE WHEN last_name is NULL THEN first_name ELSE first_name + ' ' + last_name

Answer 5

try:

SELECT first_name + ISNULL(' '+last_name, '') AS Name FROM dbo.person

This adds the space to the last name, if it is null, the entire space+last name goes to NULL and you only get a first name, otherwise you get a firts+space+last name.

this will work as long as the default setting for concatenation with null strings is set:

SET CONCAT_NULL_YIELDS_NULL ON 

this shouldn't be a concern since the OFF mode is going away in future versions of SQl Server

Answer 6

There are plenty of solutions but none covers why the original statement doesn´t work.

CASE last_name WHEN null THEN '' ELSE ' '+last_name

After the when, there is a check for a condition, which can be true, false.

If one part of a condition is null, the result of this condition will be false. To avoid this, Coalesce is the best way.