How to concat_ws multiple fields and remove duplicate separators for empty slots
concatenation, mysql, sql
Solution
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.
Problem
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.