Back to cookbooks list Articles Cookbook

How to Split a String in PostgreSQL

Problem:

You want to split a string in PostgreSQL.

Example 1:

You have a sentence, and you'd like to split it by the space character.

Solution 1:

SELECT unnest(
  string_to_array('It''s an example sentence.', ' ')
) AS parts;

The result looks like this:

parts
It's
an
example
sentence.

Discussion:

To get all parts of the sentence as elements of an array in PostgreSQL, use the string_to_array(text, delimiter) function. The text is the text you'd like to split, and the delimiter is the string (here, a space) by which you'd like to split the text. A simple use of the string_to_array(text, delimiter) function:

SELECT string_to_array('It''s an example sentence.', ' ') AS parts;

will return the following result:

parts
{It's,an,example,sentence.}
SELECT unnest(
  string_to_array('It''s an example sentence.', ' ')
) AS parts;

This will produce a column with all string parts, each in a separate row.

Example 2:

In the sentences table, there are some sentences.

sentence
This is the first sentence.
And here's the other one.

You want to split the sentences by the space character.

Solution 2:

SELECT unnest(string_to_array(sentence, ' ')) AS parts
FROM sentences;

The result looks like this:

parts
This
is
the
first
sentence.
And
here's
the
other
one.

Discussion:

Just as in the previous example, use the string_to_array(text, delimiter) and unnest(array) functions. The text should be the column name (sentence), but the delimiter is still the space (' ').  This time, you use the data from the table, so you need to use the FROM keyword along with the table name.

SELECT unnest(string_to_array(sentence, ' ')) AS parts
FROM sentences;

Example 3:

In the sentences table, there are two columns: id and sentence.

idsentence
1This is the first sentence.
2And here's the other one.

You want to split the sentences by the space character.

Solution 3:

SELECT
  id,
  unnest(string_to_array(sentence, ' ')) AS parts
FROM sentences;

The result looks like this:

idparts
1This
1is
1the
1first
1sentence.
2And
2here's
2the
2other
2one.

Discussion:

This example is very similar, but you also want to see the id column. To see this column, just add it to the SELECT list and remember to include the comma. You'll see the ID of the sentence along with the parts of the sentences in the result. For example, the first sentence is split into 5 parts, and it has the ID 1. Hence, the ID for all 5 parts in the result will be 1. The next sentence, with the ID 2, is also split into 5 parts, and each of these parts will be shown with id = 2.

Recommended courses:

Recommended articles:

See also: