MySQL CONCAT() Function

Summary: in this tutorial, you will learn how to use the MySQL CONCAT() function to concatenate multiple strings into a single string.

Introduction to the MySQL CONCAT function

The CONCAT() function allows you to concatenate multiple strings into a single string.

Here’s the basic syntax of the CONCAT() function:

CONCAT(string1, string2, string3, ...)Code language: SQL (Structured Query Language) (sql)

The CONCAT() function accepts a variable number of input strings: string1, string2, string3, …

It returns a single string that combines the string arguments string1, string2, and string3 …

If any string is NULL, the CONCAT() function returns NULL.

In addition, if you pass numbers to the CONCAT() function, it’ll convert these numbers to their equivalent strings before concatenation.

MySQL CONCAT function examples

Let’s take some examples of using the CONCAT() function.

1) Simple CONCAT function example

The following example uses the CONCAT() function to concatenate the quoted string into a single string:

SELECT 
  CONCAT('John', ' ', 'Doe') full_name;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| full_name |
+-----------+
| John Doe  |
+-----------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we use the CONCAT() function to combine three quoted strings ‘John’, ‘ ‘, and ‘Doe’ into the full name.

If you only concatenate the quoted strings, you can concatenate them by placing the strings next to each other like this:

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

2) Using MySQL CONCAT with table data

We’ll use the employees table from the sample database:

The following example uses the CONCAT function to concatenate the first name, space, and last name into the full name:

SELECT 
  CONCAT(firstName, ' ', lastName) full_name 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+
| full_name         |
+-------------------+
| Diane Murphy      |
| Mary Patterson    |
| Jeff Firrelli     |
| William Patterson |
| Gerard Bondur     |
...Code language: SQL (Structured Query Language) (sql)

3) Using MySQL CONCAT with NULL values

We’ll take the customers table for the demonstration:

The following example uses the CONCAT function to concatenate the values in the city and state columns into a single string:

SELECT 
  city, 
  state, 
  CONCAT(city, ',', state) city_state 
FROM 
  customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+---------------+---------------------------+
| city              | state         | city_state                |
+-------------------+---------------+---------------------------+
| Nantes            | NULL          | NULL                      |
| Las Vegas         | NV            | Las Vegas,NV              |
| Melbourne         | Victoria      | Melbourne,Victoria        |
| Nantes            | NULL          | NULL                      |
| Stavern           | NULL          | NULL                      |
| San Rafael        | CA            | San Rafael,CA             |
...Code language: SQL (Structured Query Language) (sql)

The output indicates that if the state is NULL, the result string will be NULL. To handle the NULL values gracefully, you can use the CONCAT_WS function.

Alternatively, you can use null-related functions such as IF and IFNULL. For example:

SELECT 
  city, 
  state, 
  CONCAT(
    city, 
    IF(state IS NULL, '', ','), 
    IFNULL(state, '')
  ) city_state 
FROM 
  customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+---------------+---------------------------+
| city              | state         | city_state                |
+-------------------+---------------+---------------------------+
| Nantes            | NULL          | Nantes                    |
| Las Vegas         | NV            | Las Vegas,NV              |
| Melbourne         | Victoria      | Melbourne,Victoria        |
| Nantes            | NULL          | Nantes                    |
| Stavern           | NULL          | Stavern                   |
...Code language: SQL (Structured Query Language) (sql)

In this example, the CONCAT() takes three arguments:

• city: The first argument.
• IF(state IS NULL, '', ',') : The second argument returns space if the state is NULL or a comma (,) otherwise.
• IFNULL(state, ''): The third argument returns a space if the state is NULL or state otherwise.

The result string will be city only if the state is NULL or city, state if the state is not NULL.

The CONCAT_WS function concatenates multiple strings into a string delimited by a specified space. It skips the NULL values. For example:

SELECT 
  city, 
  state, 
  CONCAT_WS(',', city, state) city_state 
FROM 
  customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+---------------+---------------------------+
| city              | state         | city_state                |
+-------------------+---------------+---------------------------+
| Nantes            | NULL          | Nantes                    |
| Las Vegas         | NV            | Las Vegas,NV              |
| Melbourne         | Victoria      | Melbourne,Victoria        |
| Nantes            | NULL          | Nantes                    |
...Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL CONCAT() function to concatenate multiple strings into a single string.
• The CONCAT() function returns NULL if any string argument is NULL.