PostgreSQL provides a built-in string function named STRING_TO_TABLE() that is used to convert or split the given string into a set based on the specified delimiter/splitter. For instance, using the STRING_TO_TABLE() function a sentence can be divided into words. It can be used as an alternative to the REGEXP_SPLIT_TO_TABLE().

This write-up explains how to use the STRING_TO_TABLE() function in PostgreSQL using practical examples.

How to Use the STRING_TO_TABLE() Function in Postgres?

The STRING_TO_TABLE() function accepts a string and a delimiter as arguments and splits the given string based on the specified delimiter. Here is the syntax:

STRING_TO_TABLE(str, delimiter);

The delimiter can be any character, a white space, a comma, etc.

Example 1: How Does the STRING_TO_TABLE() Function Work in Postgres?

The below example accepts a string as an argument and splits it into substrings based on the specified delimiter:

SELECT STRING_TO_TABLE('Hello Welcome to USA', ' ');

In the above query, white space is used as a delimiter:

The given string is divided into words using the space as a delimiter.

Example 2: How to Use the STRING_TO_TABLE() Function Using the Null?

In the following example, NULL will be used as the delimiter, which will split the given string and specify each character on a new line:

SELECT STRING_TO_TABLE('Hello Welcome to USA', NULL');

The output shows that the stated function accepts a string and retrieves the set of characters.

Example 3: How Does the STRING_TO_TABLE() Function Work on Table’s Data?

We will use the “employee_information” table that has been already created in the database:

SELECT * FROM employee_information;

Let’s utilize the STRING_TO_TABLE() function to split the employees email ids from the “@” symbol:

SELECT STRING_TO_TABLE(e_email, '@') 
FROM employee_information;

The output shows that the employees’ emails have been divided based on the specified delimiter, i.e., “@”.

Conclusion

The STRING_TO_TABLE() function accepts a string and a delimiter as arguments and splits the given string based on the specified delimiter. The delimiter can be any character, a white space, a comma, NULL, etc. The NULL value can be used as the delimiter to split the given string into individual characters. This write-up has explained the usage of the STRING_TO_TABLE() function using suitable examples.