PostgreSQL regexp_split_to_array() Function

The PostgreSQL regexp_split_to_array() function splits a specified string into an array using a POSIX regular expression as the separator and returns the array.

This function is similar to the string_to_array() function.

regexp_split_to_array() Syntax

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

regexp_split_to_array(string, regex[, flags]) → text[]

Parameters

string

Required. The string to split.

regex

Required. The regular expression used as delimiter.

flags

Optional. The match mode of the regular expression.

Return value

The PostgreSQL regexp_split_to_array() function splits a specified string into an array using a POSIX regular expression as the separator and returns the array.

If regex is NULL, this function will return NULL.

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

If null_string is not NULL, the members matched in the split array will be replaced with NULL.

regexp_split_to_array() Examples

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

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

 regexp_split_to_array
-----------------------
 {ab,cd,ef,gh}

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

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

 regexp_split_to_array
-----------------------
 {a,b,",",c,d}

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

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

 regexp_split_to_array
-----------------------
 {Ab,fghab,fGh}