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:

+------------------------------------+---------------------------+
| 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.

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;

city               state          city_state_using_concat    city_state_using_concat_ws  
-----------------  -------------  -------------------------  --------------------------  
Nantes             (null)         (null)                     Nantes                      
Las Vegas          NV             Las Vegas,NV               Las Vegas NV                
Melbourne          Victoria       Melbourne,Victoria         Melbourne Victoria          
Nantes             (null)         (null)                     Nantes                      
Stavern            (null)         (null)                     Stavern                     
San Rafael         CA             San Rafael,CA              San Rafael CA               
Warszawa           (null)         (null)                     Warszawa                    
Frankfurt          (null)         (null)                     Frankfurt                   
San Francisco      CA             San Francisco,CA           San Francisco CA            
NYC                NY             NYC,NY                     NYC NY      

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.