PostgreSQL – Split The String With Regex Split

Şahap Aşçı

6 years ago

In PostgreSQL we have 2 functions to split a string according to a certain character;

regexp_split_to_array and regexp_split_to_table.

regexp_split_to_array : It splits the string according to regular expression and returns its parts in an array.

regexp_split_to_table : It splits the string into pieces according to the regular expression and returns its parts in the rows of a table.

Split by space (' ') character and get an array:

SELECT regexp_split_to_array('hello world', E'\\s+') AS sentence;

1	SELECT regexp_split_to_array('hello world', E'\\s+') AS sentence;

Split by space (' ') character and get an array. Then list the first and second fields in this list separately:

SELECT sentence[1] AS first_word, sentence[2] AS second_word FROM
(
SELECT regexp_split_to_array('hello world', E'\\s+') AS sentence
) foo;

SELECT sentence[1] AS first_word, sentence[2] AS second_word FROM

(

SELECT regexp_split_to_array('hello world', E'\\s+') AS sentence

) foo;

Split by dot (.) character and get second part:

SELECT (regexp_split_to_array('www.dbtut.com', E'\\.'))[2]

1	SELECT (regexp_split_to_array('www.dbtut.com', E'\\.'))[2]

Split by space character and get it as a table:

SELECT regexp_split_to_table('hello world', E'\\s+');

1	SELECT regexp_split_to_table('hello world', E'\\s+');

Split by comma (,) and get it as a table that sorted by numbers:

SELECT number::INTEGER
FROM regexp_split_to_table('1,3,2,4,7,5,9,10', ',') AS number
ORDER BY 1;

SELECT number::INTEGER

FROM regexp_split_to_table('1,3,2,4,7,5,9,10', ',') AS number

ORDER BY 1;