- Categories:
Semi-structured Data Functions (Array/Object)
ARRAY_TO_STRING¶
Returns an input array converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).
Syntax¶
ARRAY_TO_STRING( <array> , <separator_string> )
Arguments¶
array
The array of elements to convert to a string.
separator_string
The string to put between each element, typically a space, comma, or other human-readable separator.
Returns¶
The data type of the returned value is VARCHAR
.
Usage Notes¶
A NULL argument returns NULL as a result.
To include a blank space between values, the
separator_string
must include both the separator character and the blank space (e.g.', '
). Seem the example(s) below.
Examples¶
SELECT column1, ARRAY_TO_STRING(PARSE_JSON(column1), '') AS no_separation, ARRAY_TO_STRING(PARSE_JSON(column1), ', ') AS comma_separated FROM VALUES (NULL), ('[]'), ('[1]'), ('[1, 2]'), ('[true, 1, -1.2e-3, "Abc", ["x","y"], {"a":1}]'), ('[, 1]'), ('[1, ]'), ('[1, , ,2]'); +-----------------------------------------------+---------------------------------+-------------------------------------------+ | COLUMN1 | NO_SEPARATION | COMMA_SEPARATED | |-----------------------------------------------+---------------------------------+-------------------------------------------| | NULL | NULL | NULL | | [] | | | | [1] | 1 | 1 | | [1, 2] | 12 | 1, 2 | | [true, 1, -1.2e-3, "Abc", ["x","y"], {"a":1}] | true1-0.0012Abc["x","y"]{"a":1} | true, 1, -0.0012, Abc, ["x","y"], {"a":1} | | [, 1] | 1 | , 1 | | [1, ] | 1 | 1, | | [1, , ,2] | 12 | 1, , , 2 | +-----------------------------------------------+---------------------------------+-------------------------------------------+