LIST()

Tip

Find a more recent version at Firebird 5.0 Language Reference: LIST()

Available in: DSQL, PSQL

Added in: 2.1

Changed in: 2.1.4

Description: LIST returns a string consisting of the non-NULL argument values in the group, separated either by a comma or by a user-supplied delimiter. If there are no non-NULL values (this includes the case where the group is empty), NULL is returned.

Result type: BLOB

Syntax:

LIST ([ALL | DISTINCT] expression [, separator])
ALL (the default) results in all non-NULL values to be listed. With DISTINCT, duplicates are removed, except if expression is a BLOB.

The optional separator argument may be a string literal, a parameter or a variable in versions up to 2.1.3. Starting at 2.1.4 it may be any string expression (a backport from 2.5). This makes it possible to specify e.g. ascii_char(13) as a separator.

The expression and separator arguments support BLOBs of any size and character set.

Date/time and numerical arguments are implicitly converted to strings before concatenation.

The result is a text BLOB, except when expression is a BLOB of another subtype.

The ordering of the list values is undefined.

Bug

In versions 2.1–2.1.3, the last part of the result is sometimes truncated. With a single-row set, this happens when the length gets somewhere above 4000. As the number of rows grows, the threshold climbs rapidly, so in practice this bug might not raise its head very often. It is fixed in 2.1.4.