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?

Answer 1

Given your query you can also do this:

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

Answer 2

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 equality, which should be true or false.

If one or both parts of a comparison is null, the result of the comparison will be UNKNOWN, which is treated like false in a case structure. See: https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

To avoid this, Coalesce is the best way.

Answer 3

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

Answer 4

NULL does not equal anything. The case statement is basically saying when the value = NULL .. it will never hit.
There are also several system stored procedures that are written incorrectly with your syntax. See sp_addpullsubscription_agent and sp_who2.
Wish I knew how to notify Microsoft of those mistakes as I'm not able to change the system stored procs.

Answer 5

Jason caught an error, so this works...

Can anyone confirm the other platform versions?
SQL Server:

SELECT
CASE LEN(ISNULL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName

MySQL:

SELECT
CASE LENGTH(IFNULL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName

Oracle:

SELECT
CASE LENGTH(NVL(last_name,'')) 
WHEN 0 THEN '' 
ELSE ' ' + last_name
END AS newlastName

Answer 6

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