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
ghIf 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
dYou 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