Re: CONCAT names with condition

[Rick James]

This might work:
CONCAT_WS(' ', first_name, middle_name, last_name)
if middle_name is NULL when it is missing.

mysql> SELECT CONCAT_WS(' ', 'aaa', NULL, 'bbb');
+------------------------------------+
| CONCAT_WS(' ', 'aaa', NULL, 'bbb') |
+------------------------------------+
| aaa bbb                            | -- NULL vanishes
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(' ', 'aaa', '', 'bbb');
+----------------------------------+
| CONCAT_WS(' ', 'aaa', '', 'bbb') |
+----------------------------------+
| aaa  bbb                         | -- empty string leaves the unwanted space
+----------------------------------+
1 row in set (0.00 sec)