How to use CONCAT_WS with COALESCE?

Question

I have a table users which has fields firstName and lastName. Both of them can be NULL.

I would like to select the full name, one of them, or if both are not set then default to a string. I tried the following query, but if both of them are NULL the value is not set to Unnamed, but an empty string is produced instead.

SELECT COALESCE(CONCAT_WS(' ', firstName, lastName), 'Unnamed') AS fullName FROM users;

I can't use CONCAT, because if only one of the names is filled in, I want the single name to be returned, and CONCAT will return NULL if one of the values is not set.

How could I set a default value only if both of the columns are NULL?

Answer 1

COCNAT_WS() returns the empty string if all the arguments apart from the separator are NULL.

So, one way to do what you want is:

SELECT COALESCE(NULLIF(CONCAT_WS(' ', firstName, lastName), ''), 'Unnamed') AS fullName
FROM users;

But, this might be simpler without CONCAT_WS():

select coalesce(concat(firstName, ' ', lastName),
                firstName,
                lastname,
                'Unnamed'
               ) as fullName

Answer 2

Right, if both strings are NULL, then the return value is the separator, which you have set to ' '. So you could convert that to NULL if the result of CONCAT_WS() is ' '.

SELECT COALESCE(
  NULLIF(CONCAT_WS(' ', firstName, lastName), ' '), 
  'Unnamed'
 ) AS fullName 
FROM users;