Summary: in this tutorial, you’ll learn how to use the MariaDB group_concat()
aggregate function to concatenate strings in a group.
An overview of the MariaDB group_concat() function
The MariaDB group_concat()
is an aggregate function that concatenates a group of strings into a single string.
Here is the syntax of the group_concat()
function:
group_concat(
[distinct] expression [,expression...]
[order by sort_expression [asc | desc],...]
Code language: SQL (Structured Query Language) (sql)
[separator sep]
);
In this syntax:
- The
distinct
clause eliminates duplicate strings before concatenating them. - The
order by
clause sorts the values in ascending or descending order before concatenation. - The
separator
clause specifies aseparator
between string elements in the result string. The default separator is a comma (,).
The group_concat()
returns null if either the set of input strings is empty or all arguments are null. The group_concat()
function ignores null values.
MariaDB group_concat() function examples
We’ll use the tables languages
, country_languages
, countries
from the sample database for the demonstration.
The following statement uses the group_concat()
function to return the countries and their languages:
select
countries.name country,
group_concat(language) languages
from
countries
inner join country_languages
using (country_id)
inner join languages
using (language_Id)
group by
country;
Code language: SQL (Structured Query Language) (sql)
To sort the languages in each country, you use the order by
clause in the group_concat()
function:
select
countries.name country,
group_concat(
language
order by language
) languages
from
countries
inner join country_languages
using (country_id)
inner join languages
using (language_Id)
group by
country;
Code language: SQL (Structured Query Language) (sql)
To place the official language at the beginning and other languages at the end of the list, you use the order by
clause:
select
countries.name country,
group_concat(
language
order by
official desc,
language asc
) languages
from
countries
inner join country_languages
using (country_id)
inner join languages
using (language_Id)
group by
country;
Code language: SQL (Structured Query Language) (sql)
If you want to use the separator (/
) instead of the comma (,
), you use the separator
clause as shown in the following example:
select
countries.name country,
group_concat(
language
order by
official desc,
language asc
separator '/'
) languages
from
countries
inner join country_languages
using (country_id)
inner join languages
using (language_Id)
group by
country;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you learned how to use the MariaDB group_concat()
function to concatenate strings in a group into a single string with some options.