Splitting and joining strings ​

These functions split text into arrays or rows and join array members back into text. Choose literal-delimiter functions when the separator is fixed, and regular-expression variants when the separator is a pattern.

Empty separators, NULL inputs and NULL array members have function-specific behavior. array_to_string can supply an explicit replacement for missing members.

Example: Changing a list separator ​

SELECT array_to_string(string_to_array('Ada,Grace,Linus', ','), ' | ')
FORMAT csv;

"Ada | Grace | Linus"

array_to_string ​

Join native array members in depth-first order, skipping NULL unless a replacement is supplied.

Syntax ​

array_to_string(array, delimiter[, null_string])

Arguments ​

Native Array, delimiter text, optional NULL-member replacement; nested arrays are flattened.

Returned value ​

TEXT; empty array gives empty text; NULL array or delimiter gives NULL.

Example ​

SELECT array_to_string(array(1,NULL,2),',','?') FORMAT csv;

"1,?,2"

regexp_split_to_array ​

Split text into native text fields using a Unicode regex delimiter.

Syntax ​

regexp_split_to_array(text, pattern[, flags])

Arguments ​

Existing regex syntax/flags; ignore zero-length delimiters at endpoints and immediately after a preceding match; captures are excluded.

Returned value ​

Native Array(String); empty source produces one empty field; NULL propagates.

Example ​

SELECT array_to_string(regexp_split_to_array('a b c',' +'),'|') FORMAT csv;

"a|b|c"

regexp_split_to_table ​

Split text into rows using the documented delimiter rules.

Syntax ​

FROM regexp_split_to_table(text, pattern[, flags])

Arguments ​

Same separators and flags as regexp_split_to_array; any NULL argument emits no rows.

Returned value ​

One TEXT field per row in column regexp_split_to_table; FROM-only.

Example ​

SELECT * FROM regexp_split_to_table('a,b',',') FORMAT csv;

"a"
"b"

split_part ​

Return one delimiter-separated field from text.

Syntax ​

split_part(value, delimiter, field)

Arguments ​

Positive fields count from one; negative fields count from the last field; zero raises INVALID_ARGUMENT. Empty delimiter leaves one field.

Returned value ​

Field TEXT, empty TEXT when missing, or NULL for any NULL argument.

Example ​

SELECT split_part('a::b::c','::',2) FORMAT csv;

"b"

string_to_array ​

Split text into a native Array(String), replacing exact null_string fields with NULL.

Syntax ​

string_to_array(text, delimiter[, null_string])

Arguments ​

NULL delimiter splits Unicode characters; empty delimiter retains one field; empty input gives an empty array.

Returned value ​

Native text array; nullable members when a non-NULL null_string is supplied; NULL for NULL input.

Example ​

SELECT array_to_string(string_to_array('a,b,c',','),'|') FORMAT csv;

"a|b|c"

string_to_table ​

Split text into rows using the documented delimiter rules.

Syntax ​

FROM string_to_table(text,delimiter[,null_string])

Arguments ​

NULL delimiter splits Unicode characters; empty delimiter keeps the whole nonempty input; optional marker yields SQL NULL; empty source yields zero rows.

Returned value ​

One string_to_table column containing nullable TEXT.

Example ​

SELECT * FROM string_to_table('a,b',',') FORMAT csv;

"a"
"b"