Using STRING_TO_TABLE() in PostgreSQL

Posted on by Ian

In PostgreSQL, we can use the string_to_table() function to return a set of rows, each containing a part of the string. The string is split based on the specified delimiter.

If we specify a null delimiter, then each character becomes a separate row in the output. If the delimiter string is empty, then the whole string is returned in a single row.

We also have the option of turning a specific substring into null if required.

Example

Here’s an example to demonstrate the function:

SELECT string_to_table('Cat-Dog-Bird', '-');

Result:

 string_to_table 
-----------------
 Cat
 Dog
 Bird
(3 rows)

So we can see that the string was separated into three parts and each part was returned in its own row.

In that example I specified a hyphen for the separator. Here’s an example where a space is the separator:

SELECT string_to_table('Cat Dog Bird', ' ');

Result:

 string_to_table 
-----------------
 Cat
 Dog
 Bird
(3 rows)

We can also specify multiple characters as the separator:

SELECT string_to_table('Cat, Dog, Bird', ', ');

Result:

 string_to_table 
-----------------
 Cat
 Dog
 Bird
(3 rows)

In this example I specified that a comma followed by a space is the separator.

Passing a null Separator

Specifying null for the separator results in a new row being returned for each character in the string:

SELECT string_to_table('Cat-Dog-Bird', null);

Result:

 string_to_table 
-----------------
 C
 a
 t
 -
 D
 o
 g
 -
 B
 i
 r
 d
(12 rows)

Passing an Empty String as the Separator

Specifying an empty string as the separator results in the whole string being returned in a single row:

SELECT string_to_table('Cat-Dog-Bird', '');

Result:

 string_to_table 
-----------------
 Cat-Dog-Bird
(1 row)

Converting a Substring to null

We can pass an optional third argument to specify that a certain substring is output as null in the result:

SELECT string_to_table('Cat-Dog-Bird', '-', 'Dog');

Result:

 string_to_table 
-----------------
 Cat
 null
 Bird
(3 rows)

Here I specified that the substring Dog is returned as null in the result.

If the specified value doesn’t exist in the string, then it has no effect:

SELECT string_to_table('Cat-Dog-Bird', '-', 'Bull');

Result:

 string_to_table 
-----------------
 Cat
 Dog
 Bird
(3 rows)