Return a single string by concatenating multiple strings separated by a specified separator.
MySQL CONCAT_WS() Function
Summary: in this tutorial, you will learn how to use the MySQL CONCAT_WS function to concatenate strings into a single string, separated by a specified delimiter.
Introduction to MySQL CONCAT_WS function
CONCAT_WS stands for Concatenate With Separator. The CONCAT_WS function concatenates multiple strings into a single string separated by a specified separator.
Here’s the syntax of the CONCAT_WS function:
CONCAT_WS(separator, string1, string2, string3, ...)Code language: SQL (Structured Query Language) (sql)In this syntax:
separator: This is a separator that you use to separate the strings.string1,string2,string3, ..: The strings that you want to concatenate.
The CONCAT_WS returns a single string that combines the string1, string2, string3… separated by the separator.
If the separator is NULL, the CONCAT_WS will return NULL. The CONCAT_WS function does not skip empty strings. But if does skip any NULL strings (string1, string2, string3…).
In practice, you use the CONCAT_WS function to combine values from different columns with a custom separator.
MySQL CONCAT_WS function examples
Let’s take some examples of using the CONCAT_WS() function.
1) Simple CONCAT_WS function example
The following example uses the CONCAT_WS() function to concatenate two strings with a comma:
SELECT CONCAT_WS(',', 'John', 'Doe') full_name;Code language: SQL (Structured Query Language) (sql)Output:
+-----------+
| full_name |
+-----------+
| John,Doe |
+-----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)In this example, we use the CONCAT_WS function to combine the strings 'John' and 'Doe' with a comma separator. The result is the string 'John,Doe'.
2) Using the CONCAT_WS with the table data
We’ll use the employees from the sample database for the demonstration:
The following example uses the CONCAT_WS to concatenate values from the firstNameand lastName columns of the employees table using a space as a separator:
SELECT CONCAT_WS(' ', firstName, lastName) full_name
FROM employees
ORDER BY lastName;Code language: SQL (Structured Query Language) (sql)Output:
+-------------------+
| full_name |
+-------------------+
| Gerard Bondur |
| Loui Bondur |
| Larry Bott |
| Anthony Bow |
| Pamela Castillo |
...Code language: SQL (Structured Query Language) (sql)The query returns a result set with a single column full_name containing the full names of all employees.
3) Using CONCAT_WS function with NULL values
Consider the following customers table in the sample database:
The following query uses the CONCAT_WS function to concatenate the city and state of the customers into a single string with the comma as a separator:
SELECT
customerName,
CONCAT_WS(',', city, state) address
FROM
customers
ORDER BY
customerName;Code language: SQL (Structured Query Language) (sql)Output:
+------------------------------------+---------------------------+
| customerName | address |
+------------------------------------+---------------------------+
| Alpha Cognac | Toulouse |
| American Souvenirs Inc | New Haven,CT |
| Amica Models & Co. | Torino |
| ANG Resellers | Madrid |
| Anna's Decorations, Ltd | North Sydney,NSW |
| Anton Designs, Ltd. | Madrid |
| Asian Shopping Network, Co | Singapore |
| Asian Treasures, Inc. | Cork,Co. Cork |Code language: SQL (Structured Query Language) (sql)In this example, when the state is NULL, the CONCAT_WS skips it in the result string.
Summary
- Use the
CONCAT_WSfunction to concatenate multiple strings into a single string separated by a specified separator. - The
CONCAT_WSfunction skipsNULLvalues.