MySQL Group Concat for Strings

GROUP _CONCAT function is a GROUP BY aggregate function that allows you to concatenate column values from multiple rows into a single field. It returns a string if the set group contains one or no-null column value and returns a NULL value if none can be found.

This tutorial will teach you how to use MySQL GROUP_CONCAT() function to combine strings from a group with several options.

Basic Usage

As we have mentioned, this function returns a string result with the values of the concatenated non-null values or a NULL if none exists.

The general syntax is:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

Explanation

From the above syntax, you can see that the GROUP_CONCAT function uses MySQL clauses and constraints to specify the various options:

DISTINCT: The DISTINCT clause helps remove the duplicate values in the set group bef0re the concatenation process. Consider our tutorial explaining MySQL DISTINCT to understand how it works.
ORDER BY: The next clause is the ORDER BY that helps sort the values in a specified order. The order can either be ascending or descending. If no order is specified, MySQL formats the values in ascending order.
SEPARATOR: This clause sets the string literal inserted between the group’s values upon the concatenation process. By default, MySQL uses a comma (,) to separate the values.

NOTE: The string result generated by MySQL GROUP_CONCAT() function is limited to a length the value set in the group_concat_max_len variable. This value is defined in the system and has a default value of 1024. You can change this value globally or set it in the session you need.

SET [GLOBAL | SESSION] group_concat_max_len = value;

Consider the reference below to learn more:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len

How It Works: Example

Allow me to use a simple example to explain how the GROUP_CONCAT() function works. Consider the table with a field for CHAR as:

CREATE TABLE concat (value CHAR);

Let us insert values into the table as shown in the query below:

INSERT INTO concat(value) VALUES('H'),('E'),('L'),('L'),('O');

If we perform a basic GROUP_CONCAT operation on the values in the table, we will get a string result as shown below:

SELECT GROUP_CONCAT(DISTINCT value ORDER BY value ASC SEPARATOR " ") FROM concat;

The resulting value is:

+---------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT value ORDER BY value ASC SEPARATOR " ") |
+---------------------------------------------------------------+
| E H L O |
+---------------------------------------------------------------+
1 row in set (0.01 sec)

Do you want another way to understand what happened to the result given above?

We start by removing all the duplicate values due to the MySQL DISTINCT clause that removes one L.

Next, we proceed to ORDER BY ascending order as defined in (ASC), which alters the string in the form of

HELO -> EHLO

Finally, we perform the concatenation process using a space as the separator for the set values, resulting in the string E H L O from {H, E, L, L O}.

Example Use Cases

Let us take a real database and use it to illustrate how we can implement the GROUP_CONCAT() function. In this example, we will use the Sakila database, and specifically, the addresses table from the Sakila database.

Consider the resource below to download the database for your examples:

https://dev.mysql.com/doc/index-other.html

In the addresses table of the Sakila database, you will get the district column. We can get all the unique districts separated by a pipe as shown in the query below:

SELECT GROUP_CONCAT(DISTINCT district ORDER BY district SEPARATOR "|") FROM sakila.address LIMIT 5;

The query above will display all the DISTINCT districts and order them in ascending order separated by a pipe.

NOTE: The GROUP_CONCAT() function is an aggregate function. Therefore, it is required that you specify the ORDER BY statement inside the function and not in the SELECT statement.

Conclusion

MySQL GROUP_CONCAT() function discussed in this tutorial is a useful function that allows you to create unique, sorted, and organized data from a table that may contain duplicates and unordered data.

Consider the docs or our other MySQL tutorials to learn more.