Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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.

  • Only one expression (or a column) is allowed for an input parameter.
  • Sorting with ORDER BY is available only in the the expression used as a parameter.
  • The character string used as a separator allows not only character string type but also allows other types.

Syntax

GROUP_CONCAT([DISTINCT] {col | expression}

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

             [SEPARATOR str_val])

  • expression : Operation returning numerical values or character strings
  • str_val : Character string to use as a separator
  • DISTINCT : Removes duplicate values from the result.
  • ORDER BY : Specifies the order of result values.
  • SEPARATOR : Specifies the separator to divide the result values. If you omit it, the default character, comma (,) will be used as a separator.
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'