GROUP_CONCAT Function

Description

The GROUP_CONCAT function connects the values that are not NULL in the group and returns the character string in the VARCHAR type. If there are no rows of query result or there are only NULL values, NULL will be returned.

The maximum size of the return value follows the configuration of the system parameter, group_concat_max_len. The default is 1024 bytes, the minimum value is 4 bytes and the maximum value is 33,554,432 bytes. If it exceeds the maximum value, NULL will be returned.

To remove the duplicate values, use the DISTINCT clause. The default separator for the group result values is comma (,). To represent the separator explicitly, add the character string to use as a separator in the SEPARATOR clause and after that. If you want to remove separators, enter empty strings after the SEPARATOR clause.

If the non-character string type is passed to the result character string, an error will be returned.

To use the GROUP_CONCAT function, you must meet the following conditions.

Syntax

GROUP_CONCAT([DISTINCT] {col | expression}

             [ORDER BY {col | unsigned_int} [ASC | DESC]]

             [SEPARATOR str_val])

Example

SELECT GROUP_CONCAT(s_name) FROM code;

  group_concat(s_name)

======================

  'X,W,M,B,S,G'

 

SELECT GROUP_CONCAT(s_name ORDER BY s_name SEPARATOR ':') from code;

  group_concat(s_name order by s_name separator ':')

======================

  'B:G:M:S:W:X'

 

CREATE TABLE t(i int);

INSERT INTO t VALUES (4),(2),(3),(6),(1),(5);

 

SELECT GROUP_CONCAT(i*2+1 ORDER BY 1 SEPARATOR '') FROM t;

  group_concat(i*2+1 order by 1 separator '')

======================

  '35791113'