PostgreSQL regexp_split_to_table() Function

The PostgreSQL regexp_split_to_table() function splits a specified string into a result set using the specified POSIX regular expression as the separator and returns the result set.

This function is similar to the string_to_table() function.

regexp_split_to_table() Syntax

This is the syntax of the PostgreSQL regexp_split_to_table() function:

regexp_split_to_table(string, regex[, flags]) → set of text

Parameters

string

Required. The string to split.

regex

Required. The egular expression used as delimiter.

flags

Optional. The matching mode of the regular expression.

Return value

The PostgreSQL regexp_split_to_table() function splits a specified string into a result set using the specified POSIX regular expression as the separator and returns the result set.

If regex is NULL, this function will return NULL.

If regex is an empty string, this function will return an set containing all characters of the original string.

If null_string not NULL, the members matched in the set will be replaced with NULL.

regexp_split_to_table() Examples

This example shows how to use the regexp_split_to_table() function to split the string ab cd ef gh into an set using whitespaces as separator:

SELECT regexp_split_to_table('ab cd ef  gh', '\s+');

 regexp_split_to_table
-----------------------
 ab
 cd
 ef
 gh

If regex is an empty string, all characters of the entire string will be members of the result set.

SELECT regexp_split_to_table('ab,cd', '');

 regexp_split_to_table
-----------------------
 a
 b
 ,
 c
 d

You can use the i flag in the parameter flags to to perform a insensitive-case match. for example:

SELECT regexp_split_to_table('AbcdefghabCDefGh', 'cd.', 'i');

 regexp_split_to_table
-----------------------
 Ab
 fghab
 fGh