How to Split a String in PostgreSQL
Database:
Operators:
Table of Contents
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
.
id | sentence |
---|---|
1 | This is the first sentence. |
2 | And 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:
id | parts |
---|---|
1 | This |
1 | is |
1 | the |
1 | first |
1 | sentence. |
2 | And |
2 | here's |
2 | the |
2 | other |
2 | one. |
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
.