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');
We can concatenate two quoted strings by placing one string next to the other like:
select 'MyFirstname' 'MySurname';
The result is
+----------------------+
| MyFirstname |
+----------------------+
| MyFirstnameMySurname |
+----------------------+
1 row in set (0.03 sec)
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;
We get a different result:
+---------+
| surname |
+---------+
| John |
| Laura |
| Eva |
| Andy |
| Eva |
+---------+
5 rows in set (0.02 sec)
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;
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;
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;
The result will be:
+-----------------------+
| firstname || surname |
+-----------------------+
| JohnDoe |
| LauraSmith |
| EvaBook |
| AndyNovel |
| EvaLeeds |
+-----------------------+
5 rows in set (0.02 sec)
Please check that there's no space between the firstname
and surname
, but we can add it with:
select firstname || ' ' || surname from CLIENTS;
Which shows the expected result
+------------------------------+
| firstname || ' ' || surname |
+------------------------------+
| John Doe |
| Laura Smith |
| Eva Book |
| Andy Novel |
| Eva Leeds |
+------------------------------+
5 rows in set (0.02 sec)
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;
We get the expected result
+------------------+
| firstname || id |
+------------------+
| John1 |
| Laura2 |
| Eva3 |
| Andy4 |
| Eva5 |
+------------------+
The same if we try to concatenate the creation_date
date field
select firstname || creation_date from CLIENTS;
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)
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;
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)
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;
Result
+------------------------------------------+
| firstname || surname || preferred_item |
+------------------------------------------+
| JohnDoefootball |
| LauraSmithtennis ball |
| NULL |
| AndyNovelteddy bear |
| EvaLeedsfootball |
+------------------------------------------+
5 rows in set (0.03 sec)
The result shows that the entire concatenation string is NULL
!
TLDR: a concatenation will return
NULL
if any of the strings isNULL
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;
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)
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;
- Use the
CONCAT_WS
function (concat with separator) passing the space as separator
select concat_ws(' ', firstname, surname) from CLIENTS;
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;
As before we get the expected result
+-----------------------+
| concat(firstname, id) |
+-----------------------+
| John1 |
| Laura2 |
| Eva3 |
| Andy4 |
| Eva5 |
+-----------------------+
5 rows in set (0.03 sec)
Concatenating a NULL value with the CONCAT function
What about NULL
values?
select concat(firstname, surname, preferred_item) from CLIENTS;
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)
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
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)
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;
Result
+--------+--------------------------------------------+
| region | group_concat(firstname ORDER BY firstname) |
+--------+--------------------------------------------+
| APAC | Andy,Eva,Eva |
| EMEA | John,Laura |
+--------+--------------------------------------------+
2 rows in set (0.03 sec)
And change the separator with
select region,
group_concat(firstname ORDER BY firstname SEPARATOR '$$') res
from
CLIENTS
group by region;
Result
+--------+----------------+
| region | res |
+--------+----------------+
| APAC | Andy$$Eva$$Eva |
| EMEA | John$$Laura |
+--------+----------------+
2 rows in set (0.02 sec)
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;
Result
+--------+-------------+
| region | res |
+--------+-------------+
| APAC | Andy$$Eva |
| EMEA | John$$Laura |
+--------+-------------+
2 rows in set (0.03 sec)
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;
Works!
+--------+-------+
| region | res |
+--------+-------+
| APAC | 3,4,5 |
| EMEA | 1,2 |
+--------+-------+
2 rows in set (0.02 sec)
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)
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;
In this case the behaviour is different:
+--------+----------------------+
| region | res |
+--------+----------------------+
| APAC | teddy bear,football |
| EMEA | football,tennis ball |
+--------+----------------------+
2 rows in set (0.02 sec)
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)