PostgreSQL string_to_array() Function

The PostgreSQL string_to_array() function splits a specified string into an array with the specified delimiter and returns the array.

This function is similar to the regexp_split_to_array() function.

string_to_array() Syntax

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

string_to_array(string TEXT, delimiter TEXT) -> ARRAY

or

string_to_array(string TEXT, delimiter TEXT, null_string TEXT) -> ARRAY

Parameters

string

Required. The string to split.

delimiter

Required. The delimiter.

null_string

Optional. a string. Elements in the array that equals it will be replaced with NULL.

Return value

The PostgreSQL string_to_array() function returns an array whose elements are all parts of the string string splited with the delimiter delimiter.

If delimiter is NULL, all characters in the string will be members of the result array.

If delimiter is an empty string, the entire string will be the only member of the result array.

If null_string is not NULL, elements matching it in the split array will be replaced with NULL.

string_to_array() Examples

This example shows how to use the string_to_array() function to split a string into an array with a delimiter:

SELECT string_to_array('ab,cd,ef,gh', ',');

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

If delimiter is NULL, all characters in the string will be members of the result array.

SELECT string_to_array('ab,cd,ef,gh', NULL);

        string_to_array
-------------------------------
 {a,b,",",c,d,",",e,f,",",g,h}

Here, since delimiter is NULL, the members of the result array are all of the characters in the string.

If delimiter is an empty string, the entire string will be the only member of the result array.

SELECT string_to_array('ab,cd,ef,gh', '');

 string_to_array
-----------------
 {"ab,cd,ef,gh"}

If null_string is not NULL, elements matching it in the split array will be replaced with NULL.

SELECT string_to_array('ab,cd,ef,gh', ',', 'cd');

 string_to_array
-----------------
 {ab,NULL,ef,gh}