Contents
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 (stringtext,delimitertext[,null_stringtext] ) →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
- PostgreSQL 9.1
- optional third parameter specifying a string to be treated as a
NULLvalue added (commit 33f43725)
- optional third parameter specifying a string to be treated as a
- PostgreSQL 7.4
- added (commit b3c0551e)
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 arbitrary CSV strings:
postgres=# SELECT string_to_array('foo,bar,baz,"baz,boo"', ',');
string_to_array
-------------------------------
{foo,bar,baz,"\"baz","boo\""}
(1 row)
References
- PostgreSQL documentation: Array Functions