PostgreSQL 18 reference · String Functions

PostgreSQL STRING_TO_ARRAY() Function

Split a string on a delimiter into a text array. A third argument turns matching fields into NULL.

PostgreSQL 18

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

Syntax

string_to_array(string, delimiter, null_string)

Parameter	Description
`string`	The input text.
`delimiter`	The separator; `NULL` splits into single characters.
`null_string` optional	Fields equal to this become `NULL`.

A text[] array.

Split into an array

SELECT string_to_array('red,green,blue', ',') AS colors;

Output

      colors
------------------
 {red,green,blue}
(1 row)

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

One row per value

SELECT trim(tag) AS tag
FROM unnest(string_to_array('sql, json , arrays', ',')) AS t(tag);

Output

  tag
--------
 sql
 json
 arrays
(3 rows)

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

SPLIT_PART() · Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.
ARRAY_TO_STRING() · Join array elements into a string with a separator, optionally writing NULLs as a given text.
UNNEST() · Expand an array into a set of rows, one per element. With several arrays it zips them into columns.
REGEXP_SPLIT_TO_TABLE() · Split a string on a regular expression and return one row per piece. regexp_split_to_array() returns an array.
SQL String Functions · tutorial