How to Add a Separator to a Concatenated String in MySQL – CONCAT_WS()

In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).

A common usage of the CONCAT_WS() function is to create a comma-delimited list.

Here’s an example:

SELECT CONCAT_WS(',','Sydney', 'Australia') AS Location;

Result:

+------------------+
| Location         |
+------------------+
| Sydney,Australia |
+------------------+

And you can add a space in there if you want:

SELECT CONCAT_WS(', ','Sydney', 'Australia') AS Location;

Result:

+-------------------+
| Location          |
+-------------------+
| Sydney, Australia |
+-------------------+

The Separator

There’s nothing to say that the separator must be a comma. The separator can be any string.

Here’s the same example as the previous one, except this one uses a different separator.

SELECT CONCAT_WS(' - ','Paris', 'France') AS Location;

Result:

+----------------+
| Location       |
+----------------+
| Paris - France |
+----------------+

A Database Example

Here’s an example of retrieving data from a database, and combining two columns into one, separated by a comma:

SELECT CONCAT_WS(', ', city.Name, country.Name ) AS Location
FROM city
INNER JOIN country ON
city.CountryCode = country.Code
WHERE country.Code = 'NZL';

Result:

+---------------------------+
| Location                  |
+---------------------------+
| Auckland, New Zealand     |
| Christchurch, New Zealand |
| Manukau, New Zealand      |
| North Shore, New Zealand  |
| Waitakere, New Zealand    |
| Wellington, New Zealand   |
| Dunedin, New Zealand      |
| Hamilton, New Zealand     |
| Lower Hutt, New Zealand   |
+---------------------------+

NULL Values

If any of the arguments is a NULL value, MySQL will skip that value and its separator, but it will still process the others.

Example:

SELECT CONCAT_WS(', ','Auckland', NULL, 'New Zealand') AS Location;

Result:

+-----------------------+
| Location              |
+-----------------------+
| Auckland, New Zealand |
+-----------------------+

NULL Value Separator

If the separator itself is a NULL value, the concatenation operation will return NULL.

Example:

SELECT CONCAT_WS(NULL,'Auckland', NULL, 'New Zealand') AS Location;

Result:

+----------+
| Location |
+----------+
| NULL     |
+----------+

This is one of the differences between MySQL and T-SQL (SQL Server, Azure). In T-SQL, if the separator is a NULL value, the the string values are still concatenated, but without a separator.