T-SQL CASE Clause: How to specify WHEN NULL

user_390482 0
Viewed 574236

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?

tsql
17 Answers

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

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

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.

user_4644310

You can use like this:

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