In Postgresql, the regexp_split_to_table() function is used to split the specified string into a table column using the specified POSIX regular expressions. The strings as well as the POSIX regular expression are provided to the function as an argument. This expression will act as a separator for the string elements in the table column. A flag can also be specified in the function. This function works the same as the string_to_table() function.
Let’s get deep into the details of the regexp_split_to_table() function.
PostgreSQL regexp_split_to_table() Function
As discussed earlier, the regexp_split_to_table() function splits the given string into a table column placing the regular expressions as separators. There also exists a third argument, which is completely optional, that can control some specific behavior of the function. The function returns a table. Let's have a look at the basic syntax of the function:
regexp_split_to_table(String, Regexp[, Flag]);
In the above syntax:
● The String is the main string that needs to be split.
● The second argument, Regexp, is the POSIX reg expression that will act as a separator.
● The third argument is the flag that can slightly mold the functionality of the function. For example, the ”i” flag will consider the sensitive matching.
The function returns a table that contains the string split in the columns with the regular expressions as separators. Below are some of the important things about the value of Regex:
● If the Regex is NULL the function will also return NULL.
● If the value of Regex is an empty string, the function will simply return all the characters of the original string.
Let’s move on to the examples of the regexp_split_to_table() function to understand it better.
Consider the following query as an example:
SELECT regexp_split_to_table('bats Eaten gate Atone', '\s+');
In this query, the string, 'bats Eaten gate Atone', will be separated by whitespaces as the regular expression is ”\s+” which gives the white spacing between the string elements and separates them in column rows. The output of the query is:
Consider the case where the Regex is an empty string. The query is as follows:
SELECT regexp_split_to_table('bats Eaten gate Atone', '');
The output of this query will separate all the characters of the string in the columns of a table like this:
Now consider using the optional flags in the query. In the below query, we will be using the “i” flag.
SELECT regexp_split_to_table('bats Eaten gate Atone', 'at.', 'i');
The “i” flag will match the regex for the case-insensitive characters in the string and then accordingly return the value like this:
In the above output, the” i” flag has found “at” in the string and its next character(represented by “.”) and separated them in the columns of a table.
This was all about the regexp_split_to_table() function.
The regexp_split_to_table() function takes the string, the regexp, and the flag(if any) as arguments and returns a table column that contains the string elements/characters with the Regexp as separators. In this article, we have learned about the regexp_split_to_table() function in PostgreSQL. This function works the same as the string_to_table() function does.