MariaDB group_concat() Function

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 a separator 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.