T-SQL CASE Clause: How to specify WHEN NULL

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?

17 Answers

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

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)

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:

To avoid this, Coalesce is the best way.

Given your query you can also do this:

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

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 

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

The issue is that NULL is not considered to be equal to anything even not to itself, but the strange part is that is also not not equal to itself.

Consider the following statements (which is BTW illegal in SQL Server T-SQL but is valid in My-SQL, however this is what ANSI defines for null, and can be verified even in SQL Server by using case statements etc.)

SELECT NULL = NULL -- Results in NULL

SELECT NULL <> NULL -- Results in NULL

So there is no true/false answer to the question, instead the answer is also null.

This has many implications, for example in

1. CASE statements, in which any null value will always use the ELSE clause unless you use explicitly the WHEN IS NULL condition (NOT the WHEN NULL condition )
2. String concatenation, as
   SELECT a + NULL -- Results in NULL
3. In a WHERE IN or WHERE NOT IN clause, as if you want correct results make sure in the correlated sub-query to filter out any null values.

One can override this behavior in SQL Server by specifying SET ANSI_NULLS OFF, however this is NOT recommended and should not be done as it can cause many issues, simply because deviation of the standard.

(As a side note, in My-SQL there is an option to use a special operator <=> for null comparison.)

In comparison, in general programming languages null is treated is a regular value and is equal to itself, however the is the NAN value which is also not equal to itself, but at least it returns 'false' when comparing it to itself, (and when checking for not equals different programming languages have different implementations).

Note however that in the Basic languages (i.e. VB etc.) there is no 'null' keyword and instead one uses the 'Nothing' keyword, which cannot be used in direct comparison and instead one needs to use 'IS' as in SQL, however it is in fact equal to itself (when using indirect comparisons).

Found a solution to this. Just ISNULL the CASE statement:

ISNULL(CASE x WHEN x THEN x ELSE x END, '') AS 'BLAH' 

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

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 

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 

I tried casting to a string and testing for a zero-length string and it worked.

CASE WHEN LEN(CAST(field_value AS VARCHAR(MAX))) = 0 THEN DO THIS END AS field 

You can use IsNull function

select isnull(rtrim(ltrim([FirstName]))+' ','') + isnull(rtrim(ltrim([SecondName]))+' ','') + isnull(rtrim(ltrim([Surname]))+' ','') + isnull(rtrim(ltrim([SecondSurname])),'') from TableDat 

if one column is null you would get an empty char

Compatible with Microsoft SQL Server 2008+

Use the CONCAT function available in SQL Server 2012 onward.

SELECT CONCAT([FirstName], ' , ' , [LastName]) FROM YOURTABLE 

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.

In SQL Server 2017, Microsoft introduced a Concatenate With Separator function, for just your situation:

SELECT CONCAT_WS(' ', first_name, last_name) FROM dbo.person 

CONCAT_WS skips NULL values, but not empty strings.

Interestingly, MySQL introduced CONCAT_WS over a decade earlier.

You can use like this:

CASE IsNull(last_name,'') WHEN '' THEN 'Max' ELSE 'Peter' END AS Name