REGEXP_SPLIT_TO_ARRAY

Split the input string at every match of a regular expression and return the parts as an array.

Category: regexReturns: ARRAY<VARCHAR>Dialect: PostgreSql

Syntax

REGEXP_SPLIT_TO_ARRAY(str, pattern [, flags])

Description

## Overview Splits the input string at every non-overlapping match of the regular expression and returns the resulting parts as an array. Delimiter matches themselves are not included in the output. Use REGEXP_SPLIT_TO_ARRAY when the delimiter is a pattern (runs of whitespace, one of several punctuation characters, a structured delimiter with optional padding) rather than a fixed substring. When the delimiter is literal, SPLIT is faster and clearer. ## Behavior - Returns NULL when either argument is NULL. - Returns a single-element array containing the input when the pattern does not match. - Adjacent delimiters or delimiters at the edges produce empty-string elements. - Zero-width matches advance one character at a time to ensure termination. - Pattern compilation errors raise an error at query time. - Operates on Unicode code points. ## Regex flavor - Rust regex crate, Perl-compatible for most common features. - Supports anchors, character classes, alternation, quantifiers, capturing groups, Unicode classes. - Does NOT support backreferences in the pattern. - Does NOT support lookahead or lookbehind. - Flags: 'i', 'c', 'n'/'s', 'm'. The 'g' flag has no effect because splitting is inherently global. ## Compatibility - Matches the common SQL REGEXP_SPLIT_TO_ARRAY semantics.

Parameters

Name	Type	Description
`str`		Specifies the input string to split.
`pattern`		Specifies the regular expression used as the delimiter. Each non-overlapping match of the pattern defines a split point.
`flags`		Specifies match flags: 'i' (case-insensitive), 'c' (case-sensitive), 'n'/'s' (dot matches newline), 'm' (multi-line).

Examples

-- Split by whitespace
SELECT REGEXP_SPLIT_TO_ARRAY('hello world foo', '\s+');  -- ['hello', 'world', 'foo']

-- Split by comma with optional surrounding spaces
SELECT REGEXP_SPLIT_TO_ARRAY('a, b,  c', ',\s*');  -- ['a', 'b', 'c']

-- Split by any of several delimiters
SELECT REGEXP_SPLIT_TO_ARRAY('one;two:three', '[;:]');  -- ['one', 'two', 'three']

-- No match returns a single-element array
SELECT REGEXP_SPLIT_TO_ARRAY('hello', '[0-9]+');  -- ['hello']

-- Empty-string elements appear when delimiters are adjacent
SELECT REGEXP_SPLIT_TO_ARRAY('a,,b', ',');  -- ['a', '', 'b']

-- NULL propagates
SELECT REGEXP_SPLIT_TO_ARRAY(CAST(NULL AS VARCHAR), ',');  -- NULL

Pitfalls

No limit argument is available. Pathological inputs with millions of delimiters produce huge arrays. Cap input length or switch to a streaming set-returning form.
Adjacent delimiters produce empty-string elements. Filter them out with ARRAY_FILTER or a regex that permits consecutive matches if you want compact output.
A zero-width pattern (for example '\b') can split at every character boundary. Use an explicit non-empty delimiter.
REGEXP_SPLIT_TO_ARRAY is a regex operation; for literal delimiters, SPLIT or STRING_TO_ARRAY is cheaper and clearer.