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