GROUP_CONCAT - concatenate strings in mysql command

In this tutorial, you will learn how to use the MySQL GROUP_CONCAT function to concatenate strings from a group of values with various options.

The following illustrates the GROUP_CONCAT function:

SYNTAX:

GROUP_CONCAT(DISTINCT expression
ORDER BY {column_name | usinged_integer | expression}
SEPARATOR sep);

• You specify the DISTINCT clause to eliminate duplicate values in a group before combining values.
• The ORDER BY clause allows you to sort the values in ascending or descending order before concatenating values. 
• The ORDER BY clause sort the values in ascending ( ASC) order by default. If you want to sort the values in the descending order, you need to specify the DESC explicitly.
• The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function use a comma (,) as the default separator.
• The GROUP_CONCAT function ignores NULL values. It returns NULL if there was no matching row found or there were no non-NULL values. The GROUP_CONCAT function returns a binary or non-binary string, which depends on the arguments. The maximum length of the return string is 1024 by default. You can extend the returned value’s length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.

Example : 

To get all countries where customers locate as a comma-separated string, you use the GROUP_CONCAT function as follows:

SELECT GROUP_CONCAT(DISTINCT country ORDER BY country)FROM customers;

Result :

                             

However, some customers locate in the same country. To remove the duplicate country’s names, you add the DISTINCT clause as the following query:

SELECT GROUP_CONCAT(DISTINCT country) FROM customers;

Result : 

                         

It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY clause as follows:

SELECT GROUP_CONCAT(DISTINCT country ORDER BY country) FROM customers;

Result :

                         

To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATOR clause as the following query:

SELECT GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ';') FROM customers;

Result :