How to concat_ws multiple fields and remove duplicate separators for empty slots

Question

When you CONCAT_WS(' ',field1,field2,field3) in MySQL and if one of the fields is empty, not null, you get multiple separators.

An example can be:

John[space][space][space]Doe[space]III.

How can I make sure there is only one separator.

Answer 1

Do it like this:

CONCAT_WS(' ', NULLIF(field1, ''), NULLIF(field2, ''), NULLIF(field3, ''));

CONCAT_WS will skip any null values, and by using NULLIF any empty ones too.

Note: You can't regex replace. MySQL does not support it.