# PostgreSQL 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 charac…

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()](<https://www.commandprompt.com/education/postgresql-string_to_table-function-with-examples>) 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.

 **Example**

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.

##  **Conclusion**

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-regexp_split_to_table-function/)