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 (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
- PostgreSQL 9.1
- optional third parameter specifying a string to be treated as a
NULL
value 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