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

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

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

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 4

Given your query you can also do this:

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

Answer 5

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 6

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 7

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).

Answer 8

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

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

Answer 9

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

Answer 10

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 11

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 12

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 

Answer 13

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+

Answer 14

Use the CONCAT function available in SQL Server 2012 onward.

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

Answer 15

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 16

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.

Answer 17

You can use like this:

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

Answer 18

Starting with SQL Server 2022 you can use the IS [NOT] DISTINCT FROM predicate (see here) and you could re-write the OP's statement like so:

SELECT first_name +
   CASE when last_name is not distinct from null THEN '' ELSE ' ' + last_name END AS Name 
FROM dbo.person