Arrays and seriesPostgreSQL only

STRING_TO_ARRAY

Splits a string by a separator into an array of parts.

Syntax

STRING_TO_ARRAY(<text>, <separator>)
STRING_TO_ARRAY(<text>, <separator>, <null string>)

Like SPLIT_PART, but it returns all the parts at once — as an array. You then work with it like a list: take an element by its number, count elements with CARDINALITY or expand them into rows with UNNEST.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

A string into an array

SELECT STRING_TO_ARRAY('sql,postgres,database', ',') AS tags;

tags
["sql","postgres","database"]

An element by number, and the count

SELECT (STRING_TO_ARRAY('sql,postgres,database', ','))[2] AS second_tag,

CARDINALITY(STRING_TO_ARRAY('sql,postgres,database', ',')) AS tag_count;

second_tag	tag_count
postgres	3

The parts of a phone number

SELECT name,

STRING_TO_ARRAY(phone, ' ') AS phone_parts

FROM employees;

name	phone_parts
Emma	["+44","7700","900123"]
Liam	NULL
Olivia	["+44","7700","900456"]
Noah	["+44","7700","900789"]
Sophia	NULL

Watch out

Spaces next to the separator stay in the parts: STRING_TO_ARRAY('a, b', ',') gives a and b. Make ', ' the separator — then you get a and b.

An empty string gives an empty array, and NULL gives NULL.

The third argument says which text counts as NULL: STRING_TO_ARRAY('a,,b', ',', '') gives the array a, NULL, b.

Related functions

SPLIT_PART UNNEST ARRAY_AGG