Search code examples
mysqlsqlconcatenationcoalesceconcat-ws

How to use CONCAT_WS with COALESCE?

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?

Solution

  • 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