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?

score 4 · Answer 1 · answered Sep 01 '19 at 20:05

4

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;

answered Sep 01 '19 at 20:05

Bill Karwin

538,548
86
673
828

Do I understand it right, that the spaces are trimmed to an empty string by MySQL? If I use empty string as the second argument to `NULLIF` it works the same as if I use string of 5 spaces. – Dharman Sep 01 '19 at 20:16
`SELECT ''=' ' AS a;` returns true, but `SELECT ' a '='a' AS a;` returns false. Why? – Dharman Sep 01 '19 at 20:19
1

Notice `SELECT 'a'='a ';` also returns true (that is, trailing spaces are ignored). Read https://dev.mysql.com/doc/refman/8.0/en/char.html for details on how trailing spaces are handled. There's a surprising amount of complexity there. – Bill Karwin Sep 01 '19 at 20:48

score 2 · Accepted Answer · edited Sep 02 '19 at 03:55

2

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

edited Sep 02 '19 at 03:55

Caius Jard

72,509
5
49
80

answered Sep 01 '19 at 20:52

Gordon Linoff

1,242,037
58
646
786

Is this the same as Bill's answer, but with more correct explanation? – Dharman Sep 01 '19 at 20:56
@Dharman . . . It compares the result of `CONCAT_WS()` to the empty string, not to the separator. – Gordon Linoff Sep 01 '19 at 21:03
As I read the manual, that won't work, depending on the `sql_mode` and/or the collation of the strings. – Bill Karwin Sep 01 '19 at 21:37

How to use CONCAT_WS with COALESCE?

2 Answers2