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.