REGEXP_SPLIT_TO_TABLE

Split the input string at every regex match and return the parts as rows (set-returning function).

Category: regexReturns: SETOF VARCHARDialect: PostgreSql

Syntax

REGEXP_SPLIT_TO_TABLE(str, pattern [, flags])

Description

## Overview Set-returning variant of REGEXP_SPLIT_TO_ARRAY that emits one row per resulting substring. Use it in FROM clauses or with LATERAL to 'unnest' delimiter-separated text fields into rows without an intermediate array. This is usually the most natural form when downstream processing wants per-token rows. ## Behavior - Produces no rows when either argument is NULL. - When the pattern does not match, produces a single row containing the whole input string. - Adjacent delimiters or delimiters at the edges produce rows with empty-string values. - 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_TABLE 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 whitespace-separated tokens into rows
SELECT REGEXP_SPLIT_TO_TABLE('hello world foo', '\s+');

-- Split a CSV string into rows
SELECT REGEXP_SPLIT_TO_TABLE('a,b,c,d', ',');

-- Split by either ':' or ';'
SELECT REGEXP_SPLIT_TO_TABLE('one;two:three', '[;:]');

-- Use in a FROM clause with LATERAL to split per source row
SELECT t.id, s.part
FROM (VALUES (1, 'a-b-c'), (2, 'x-y')) AS t(id, data),
     LATERAL REGEXP_SPLIT_TO_TABLE(t.data, '-') AS s(part);

-- No match produces a single row containing the original string
SELECT REGEXP_SPLIT_TO_TABLE('hello', '[0-9]+');  -- one row: 'hello'

-- NULL input produces no rows
SELECT * FROM REGEXP_SPLIT_TO_TABLE(CAST(NULL AS VARCHAR), ',');

Pitfalls

REGEXP_SPLIT_TO_TABLE is a set-returning function. Calling it in a scalar context produces a cartesian explosion. Use it in a FROM clause or inside LATERAL.
NULL inputs produce zero rows. LEFT JOIN LATERAL is the right tool when you want to preserve outer rows whose split produced nothing.
Adjacent delimiters yield empty-string rows. Filter them explicitly if empty tokens are not desired.
Zero-width patterns can split at every character boundary, producing one row per character. Use explicit non-empty delimiters.