DEV Community

Francesco Tisiot
Francesco Tisiot

Posted on • Originally published at ftisiot.net

How to CONCAT in MySQL

One of the most common tasks with strings is concatenation! This blog post showcases several techniques to perform it with MySQL.

If you need a FREE MySQL database? ๐Ÿฆ€ Check Aiven's FREE plans! ๐Ÿฆ€
If you need to optimize your SQL query? ๐Ÿง Check EverSQL! ๐Ÿง

String concatenation in MySQL by placing strings next to each other

Let's take a basic example: a table containing firstname and surname fields we want to concatenate:

CREATE TABLE CLIENTS (
    id INT PRIMARY KEY, 
    firstname VARCHAR(100), 
    surname VARCHAR(100),
    region VARCHAR(100),
    creation_date date,
    preferred_item VARCHAR(100)
    );
INSERT INTO CLIENTS VALUES 
    (1, 'John', 'Doe', 'EMEA', '2024-01-01', 'football'),
    (2, 'Laura', 'Smith', 'EMEA', '2024-02-01', 'tennis ball'),
    (3, 'Eva', 'Book', 'APAC', '2024-01-03', null),
    (4, 'Andy', 'Novel', 'APAC', '2024-02-05', 'teddy bear'),
    (5, 'Eva', 'Leeds', 'APAC', '2024-02-01', 'football');
Enter fullscreen mode Exit fullscreen mode

We can concatenate two quoted strings by placing one string next to the other like:

select 'MyFirstname' 'MySurname';
Enter fullscreen mode Exit fullscreen mode

The result is

+----------------------+
| MyFirstname          |
+----------------------+
| MyFirstnameMySurname |
+----------------------+
1 row in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

We can see that:

  • the result is the correct concatenation
  • the column name is the first quoted string

However, if we apply the same principle to standard columns instead of quoted strings with:

select firstname surname from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

We get a different result:

+---------+
| surname |
+---------+
| John    |
| Laura   |
| Eva     |
| Andy    |
| Eva     |
+---------+
5 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

The result showcases only the firstname in the results, with the surname being included as the result column heading.

TLDR: concatenating strings in MySQL by placing strings next to each other is possible only if we are dealing with quoted strings.

Concatenating a non string

Is it possible with the same method to concatenate a non string?

select 'MyFirstname' 1;
Enter fullscreen mode Exit fullscreen mode

Nope, we get ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Concatenating a NULL

What about concatenating a NULL value?

select 'MyFirstname' NULL;
Enter fullscreen mode Exit fullscreen mode

Same error ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

String concatenation in MySQL with the || operator

We can also concatenate firstname and surname using the || operator with:

select firstname || surname from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

The result will be:

+-----------------------+
| firstname || surname  |
+-----------------------+
| JohnDoe               |
| LauraSmith            |
| EvaBook               |
| AndyNovel             |
| EvaLeeds              |
+-----------------------+
5 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

Please check that there's no space between the firstname and surname, but we can add it with:

select firstname || ' ' || surname from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

Which shows the expected result

+------------------------------+
| firstname || ' ' || surname  |
+------------------------------+
| John Doe                     |
| Laura Smith                  |
| Eva Book                     |
| Andy Novel                   |
| Eva Leeds                    |
+------------------------------+
5 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

Concatenating a non string

What about concatenating strings with other fields that are not a string? If, for example, we try to concat the firstname and id (an integer) with the following:

select firstname || id from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

We get the expected result

+------------------+
| firstname || id  |
+------------------+
| John1            |
| Laura2           |
| Eva3             |
| Andy4            |
| Eva5             |
+------------------+
Enter fullscreen mode Exit fullscreen mode

The same if we try to concatenate the creation_date date field

select firstname || creation_date from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

Result:

+-----------------------------+
| firstname || creation_date  |
+-----------------------------+
| John2024-01-01              |
| Laura2024-02-01             |
| Eva2024-01-03               |
| Andy2024-02-05              |
| Eva2024-02-01               |
+-----------------------------+
5 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

However we might want to add a function to the date to get alternative formats from the default, for example the DATE_FORMAT function below converts the date in a weird format separating years, months and days with ..

select firstname || DATE_FORMAT(creation_date, '..%Y..%M..%D') from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

Results

+---------------------------------------------------------+
| firstname || DATE_FORMAT(creation_date, '..%Y..%M..%D') |
+---------------------------------------------------------+
| John..2024..January..1st                                |
| Laura..2024..February..1st                              |
| Eva..2024..January..3rd                                 |
| Andy..2024..February..5th                               |
| Eva..2024..February..1st                                |
+---------------------------------------------------------+
5 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

Concatenating a NULL value

What does it happen if we try to concatenate a NULL value? If you check the dataset created, the row with id=3 for Eva Book has a preferred_item equal to NULL, let's check the output of concatenating the firstname, surname and preferred_item:

select firstname || surname || preferred_item  from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

Result

+------------------------------------------+
| firstname || surname || preferred_item   |
+------------------------------------------+
| JohnDoefootball                          |
| LauraSmithtennis ball                    |
| NULL                                     |
| AndyNovelteddy bear                      |
| EvaLeedsfootball                         |
+------------------------------------------+
5 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

The result shows that the entire concatenation string is NULL!

TLDR: a concatenation will return NULL if any of the strings is NULL

String concatenation in MySQL with the CONCAT function

Another option is to use MySQL Concat function. Let's try with the basic firstname and surname example:

select concat(firstname, surname) from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

The results are in line with the previous || example, not having any additional separator added into the resulting string.

+----------------------------+
| concat(firstname, surname) |
+----------------------------+
| JohnDoe                    |
| LauraSmith                 |
| EvaBook                    |
| AndyNovel                  |
| EvaLeeds                   |
+----------------------------+
5 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

How can we add a space in between? We have two options:

  • Add a space to the CONCAT function
select concat(firstname, ' ' ,surname) from CLIENTS;
Enter fullscreen mode Exit fullscreen mode
  • Use the CONCAT_WS function (concat with separator) passing the space as separator
select concat_ws(' ', firstname, surname) from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

Concatenating a non string with the CONCAT function

What happens if we include non strings in the CONCAT function?

select concat(firstname, id) from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

As before we get the expected result

+-----------------------+
| concat(firstname, id) |
+-----------------------+
| John1                 |
| Laura2                |
| Eva3                  |
| Andy4                 |
| Eva5                  |
+-----------------------+
5 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

Concatenating a NULL value with the CONCAT function

What about NULL values?

select concat(firstname, surname, preferred_item)  from CLIENTS;
Enter fullscreen mode Exit fullscreen mode

As before, if any of the source strings is NULL, the result of the concatenation is NULL

+--------------------------------------------+
| concat(firstname, surname, preferred_item) |
+--------------------------------------------+
| JohnDoefootball                            |
| LauraSmithtennis ball                      |
| NULL                                       |
| AndyNovelteddy bear                        |
| EvaLeedsfootball                           |
+--------------------------------------------+
5 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

String aggregation via concatenation in MySQL with the GROUP_CONCAT function

If we need to concatenate all the strings belonging to the same group, we can use MySQL GROUP_CONCAT function.

If, in our example, we want to concatenate the firstname of all the people grouped by their region we can write the following:

select region,
    group_concat(firstname)
from
    CLIENTS
group by region
Enter fullscreen mode Exit fullscreen mode

The result is the following, with , as the default separator:

+--------+-------------------------+
| region | group_concat(firstname) |
+--------+-------------------------+
| APAC   | Eva,Andy,Eva            |
| EMEA   | John,Laura              |
+--------+-------------------------+
2 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

We can also order how the strings are ordered in the list with:

select region,
    group_concat(firstname ORDER BY firstname)
from
    CLIENTS
group by region;
Enter fullscreen mode Exit fullscreen mode

Result

+--------+--------------------------------------------+
| region | group_concat(firstname ORDER BY firstname) |
+--------+--------------------------------------------+
| APAC   | Andy,Eva,Eva                               |
| EMEA   | John,Laura                                 |
+--------+--------------------------------------------+
2 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

And change the separator with

select region,
    group_concat(firstname ORDER BY firstname SEPARATOR '$$') res
from
    CLIENTS
group by region;
Enter fullscreen mode Exit fullscreen mode

Result

+--------+----------------+
| region | res            |
+--------+----------------+
| APAC   | Andy$$Eva$$Eva |
| EMEA   | John$$Laura    |
+--------+----------------+
2 rows in set (0.02 sec)

Enter fullscreen mode Exit fullscreen mode

We can also only list the distinct values, removing the duplication of Eva with:

select region,
    group_concat(DISTINCT firstname ORDER BY firstname SEPARATOR '$$') res
from
    CLIENTS
group by region;
Enter fullscreen mode Exit fullscreen mode

Result

+--------+-------------+
| region | res         |
+--------+-------------+
| APAC   | Andy$$Eva   |
| EMEA   | John$$Laura |
+--------+-------------+
2 rows in set (0.03 sec)
Enter fullscreen mode Exit fullscreen mode

Aggregating and concatenating a non string with the GROUP_CONCAT function

Let's try with a non string, like the id field:

select region,
    group_concat(id) res
from
    CLIENTS
group by region;
Enter fullscreen mode Exit fullscreen mode

Works!

+--------+-------+
| region | res   |
+--------+-------+
| APAC   | 3,4,5 |
| EMEA   | 1,2   |
+--------+-------+
2 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

The same if we try with creation_date

+--------+----------------------------------+
| region | res                              |
+--------+----------------------------------+
| APAC   | 2024-01-03,2024-02-05,2024-02-01 |
| EMEA   | 2024-01-01,2024-02-01            |
+--------+----------------------------------+
2 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

Aggregating and concatenating a NULL value with the GROUP_CONCAT function

What about our NULL value? Let's check it out!

select region,
    group_concat(preferred_item) res
from
    CLIENTS
group by region;
Enter fullscreen mode Exit fullscreen mode

In this case the behaviour is different:

+--------+----------------------+
| region | res                  |
+--------+----------------------+
| APAC   | teddy bear,football  |
| EMEA   | football,tennis ball |
+--------+----------------------+
2 rows in set (0.02 sec)
Enter fullscreen mode Exit fullscreen mode

Even if the row with id=3 part of the APAC group had preferred_item equal to NULL, the resulting aggregate is not NULL! This is a common behaviour for aggregate functions to ignore the NULL values.

Conclusion

Here's an handy overview of the concatenation possibilities in MySQL:

Method code works with quoted strings works with standard columns works with non strings Works with NULL aggregates results
Placing strings next to each other 'a' 'b' โœ… โŒ โŒ โŒ โŒ
| operator 'a' || 'b' โœ… โœ… โœ… โœ… โŒ
CONCAT function concat('a','b') โœ… โœ… โœ… โœ… โŒ
GROUP_CONCAT function group_concat('a','b') โœ… โœ… โœ… โœ… โœ…

Top comments (0)