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 firstName and 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_WS function to concatenate multiple strings into a single string separated by a specified separator.
• The CONCAT_WS function skips NULL values.