STRING_TO_ARRAY

Split the input string by a literal delimiter, optionally mapping a sentinel to NULL.

Category: stringReturns: ARRAY<VARCHAR>Dialect: PostgreSql

Syntax

STRING_TO_ARRAY(str, delimiter [, null_string])

Description

## Overview Splits the input string at each occurrence of the delimiter and returns the resulting parts as an array of strings. When the optional null_string argument is provided, any part whose value exactly equals null_string is replaced with SQL NULL in the output. This is useful for parsing feeds that use an explicit sentinel (such as 'NULL', 'N/A', or '\\N') to mark missing values. STRING_TO_ARRAY is the inverse of ARRAY_TO_STRING. Together they round-trip a delimited string through an array. ## Behavior - Returns NULL when the input string is NULL. - Returns a single-element array when the delimiter is not found. - Adjacent delimiters produce empty-string elements (which may then be replaced with NULL if they match null_string). - A NULL delimiter splits the input into individual Unicode code points (one character per element). - An empty-string delimiter has implementation-defined behaviour; DeltaForge treats it as a split into individual characters, matching NULL delimiter semantics. - The delimiter is literal; regex metacharacters are not interpreted. - Operates on Unicode code points. ## Compatibility - Matches the common SQL STRING_TO_ARRAY semantics, including the null_string sentinel and the NULL-delimiter single-character split mode.

Parameters

Name	Type	Description
`str`		Specifies the input string to split into array elements.
`delimiter`		Specifies the literal delimiter used to split the input. A NULL delimiter splits into individual characters.
`null_string`		Specifies a sentinel value. Any part that exactly matches this string is replaced with SQL NULL in the result array.

Examples

-- Split on comma
SELECT STRING_TO_ARRAY('a,b,c', ',');  -- ['a', 'b', 'c']

-- Map a sentinel to NULL
SELECT STRING_TO_ARRAY('a,N/A,c', ',', 'N/A');  -- ['a', NULL, 'c']

-- NULL delimiter splits into individual characters
SELECT STRING_TO_ARRAY('abc', NULL);  -- ['a', 'b', 'c']

-- Adjacent delimiters produce empty-string elements
SELECT STRING_TO_ARRAY('a,,c', ',');  -- ['a', '', 'c']

-- Multi-character delimiter
SELECT STRING_TO_ARRAY('a--b--c', '--');  -- ['a', 'b', 'c']

-- NULL input returns NULL
SELECT STRING_TO_ARRAY(CAST(NULL AS VARCHAR), ',');  -- NULL

Pitfalls

The null_string argument matches the exact element value, not a pattern. Leading or trailing spaces in the sentinel stop it from matching; TRIM the parts or use REGEXP_REPLACE upstream.
A NULL delimiter splits into individual characters. This may be surprising if you expected NULL-delimiter to mean 'do not split'. Use SPLIT or guard against NULL delimiters explicitly.
The delimiter is literal. STRING_TO_ARRAY(s, '.') splits on a literal dot. Use REGEXP_SPLIT_TO_ARRAY for pattern-based splitting.
No limit argument is available. Pathological inputs with millions of delimiters produce huge arrays.