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:

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:

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.

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:

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.

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:

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.