PostgreSQL 18 reference · String Functions

PostgreSQL REGEXP_SPLIT_TO_TABLE() Function

Split a string on a regular expression and return one row per piece. regexp_split_to_array() returns an array.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

regexp_split_to_table(string, pattern, flags)

Parameter	Description
`string`	The input text.
`pattern`	The separator as a regular expression.
`flags` optional	For example `i`.

A set of text rows.

Split on spaces or commas

SELECT word
FROM regexp_split_to_table('one  two,three', '[\s,]+') AS word;

Output

 word
-------
 one
 two
 three
(3 rows)

Run this on your computer — see “Get Started” for setup.

As an array

SELECT regexp_split_to_array('a1b22c', '\d+') AS parts;

Output

  parts
---------
 {a,b,c}
(1 row)

Run this on your computer — see “Get Started” for setup.

STRING_TO_ARRAY() · Split a string on a delimiter into a text array. A third argument turns matching fields into NULL.
SPLIT_PART() · Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.
UNNEST() · Expand an array into a set of rows, one per element. With several arrays it zips them into columns.
SQL String Functions · tutorial