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

When you get frustrated trying this:

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

Try this one instead:

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

LEN(ISNULL(last_Name,'')) measures the number of characters in that column, which will be zero whether it's empty, or NULL, therefore WHEN 0 THEN will evaluate to true and return the '' as expected.

I hope this is a helpful alternative.

I have included this test case for sql server 2008 and above:

DECLARE @last_Name varchar(50) = NULL

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

SET @last_Name = 'LastName'

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

Answer 2

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