string_to_array()

A function for splitting a string into an array

string_to_array() is a system function for splitting a string into an array using the specified delimiter.

string_to_array() was added in PostgreSQL 7.4.

Usage

string_to_array ( string text, delimiter text [, null_string text ] ) → text[]

Note that string_to_array() is not suitable for general parsing of CSV strings, as splits are made on each occurrence of the delimiter and it does not consider whether the delimiter is embedded within a quoted string.

Change history

Examples

Basic usage example for string_to_array():

postgres=# SELECT string_to_array('1|2|3', '|');
 string_to_array 
-----------------
 {1,2,3}
(1 row)

By default, NULL embedded in the string will be converted to a string literal:

postgres=# SELECT string_to_array('1|NULL|3', '|');
 string_to_array 
-----------------
 {1,"NULL",3}
(1 row)

To have it interpreted as an actual NULL value, it needs to be specified as the third parameter:

postgres=# SELECT string_to_array('1|NULL|3', '|', 'NULL');
 string_to_array 
-----------------
 {1,NULL,3}
(1 row)

Any arbitrary value may be interpreted as NULL:

postgres=# SELECT string_to_array('1|xxx|3', '|', 'xxx');
 string_to_array 
-----------------
 {1,NULL,3}
(1 row)

Note that string_to_array() is not suitable for parsing CSV strings:

postgres=# SELECT string_to_array('foo,bar,baz,"baz,boo"', ',');
        string_to_array        
-------------------------------
 {foo,bar,baz,"\"baz","boo\""}
(1 row)

Categories

Arrays, String manipulation, System function

See also

regexp_split_to_array(), array_to_string(), string_to_table()