ARRAY_TO_STRING

​

Syntax

​

Scalar

array_to_string(array, delimiter)

​

With null text (scalar)

array_join(array, delimiter, null_text)

• The optional third argument is available only with ARRAY_JOIN.

​

Array join default (scalar)

array_join(array, delimiter)

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP or ARRAY.

​

Scalar, array join default

​

With null text

​

Returns

• Type: STRING
• Possible values: Joined text.
• NULL behavior: NULL array/delimiter return NULL. NULL elements are skipped unless non-NULL null_text is supplied.

​

Scalar, With null text, Array join default

• Type: STRING
• Possible values: Joined text.
• NULL behavior: NULL array/delimiter return NULL. NULL elements are skipped unless non-NULL null_text is supplied.

​

Properties

​

Usage notes

• Nested arrays are traversed recursively.
• NULL replacement text behaves like an omitted replacement and skips NULL elements. Nested list elements are recursively joined in encounter order. Leaf types include strings, booleans, integers, FLOAT/DOUBLE and types convertible to STRING; other leaf types raise an error.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Typical behavior

SELECT array_to_string(ARRAY[1, 2, 3], ',') AS result;

SELECT array_to_string(ARRAY[1, NULL, 2], ',') AS skipped, array_join(ARRAY[1, NULL, 2], ',', 'X') AS substituted;

​

Alias calling form: ARRAY_JOIN

SELECT array_join(ARRAY[1, NULL, 2], ',') AS skipped, array_join(ARRAY[1, NULL, 2], ',', 'X') AS substituted;

​

Limitations

• Pass an ARRAY as the first argument. Reported VARCHAR widths do not limit or truncate the returned text.
• MAP and STRUCT elements are not supported.