A few months ago I posted about string concatenation with MySQL using the CONCAT function and in this follow up post look at the CONCAT_WS function which glues the fields together with the specified string. CONCAT_WS means CONCATenate With Separator i.e. join the fields together with a specified separator string.
Example data
The example table used in this post is called "products" and contains the following data, which is the same as in the original post:
+------------+------+--------------------+--------+ | product_id | name | description | price | +------------+------+--------------------+--------+ | 1 | Foo | Blah blah foo blah | 55.00 | | 2 | Bar | Blah blah bar blah | 102.00 | | 3 | Baz | Blah blah baz blah | 87.50 | | 4 | Bat | Blah blah bat blah | 42.00 | +------------+------+--------------------+--------+
Using concat_ws to concatenate fields and strings
CONCAT_WS takes multiple arguments, the first being the string that glues the rest of the fields together. In my original post I joined the strings together with a hyphen surrounded by spaces with CONCAT ("SELECT CONCAT(name, ‘ – ‘, description) FROM products;") and this can be done instead with CONCAT_WS like so:
SELECT CONCAT_WS(' - ', name, description) FROM products;
The resulting data would look like this:
+-------------------------------------+
| CONCAT_WS(' - ', name, description) |
+-------------------------------------+
| Foo - Blah blah foo blah |
| Bar - Blah blah bar blah |
| Baz - Blah blah baz blah |
| Bat - Blah blah bat blah |
+-------------------------------------+
This can be a lot simpler to join multiple fields than using CONCAT if you are wanting to concatenate more than two fields.
