Concatenation Functions

• CONCAT(): Combines two or more strings into a single string.
• CONCAT_WS(): Combines multiple strings into a single string with 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_WSfunction 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, ...)

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')

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 employeestable using a space as a separator:

SELECT CONCAT_WS(' ', firstName, lastName) full_name
FROM employees
ORDER BY lastName;

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_namecontaining 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;

Output:

In this example, when the state is NULL, the CONCAT_WS skips it in the result string.

Another Example comparing the CONCAT and CONCAT_WS in view of null values

SELECT
  city,
  state,
  CONCAT(city, ',', state) city_state_using_concat,
  concat_ws( ' ', city,state) city_state_using_concat_ws
FROM
  customers;

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.