split_part()
split_part() is a function for splitting a string on a delimiter and returning one of the fields.
split_part() was added in PostgreSQL 7.3.
Usage
split_part (stringTEXT,delimiterTEXT,nINTEGER) →TEXT
split_part() can be used to extract a single field from a string formatted with a specified delimiter.
From PostgreSQL 14, it's possible to specify the field to be extracted as a negative number.
Note that split_part() is not suitable for general parsing of CSV strings, as splits on each occurrence of the delimiter and does not consider whether the delimiter is embedded within a quoted string.
To split a string on a delimiter and return all elements, see:
regexp_split_to_array()regexp_split_to_table()string_to_array()string_to_table()
Change history
- PostgreSQL 14
- support for negative indexes added (commit ec0294fb)
- PostgreSQL 7.3
Examples
Obtain the first element of a string with values delimited by |:
postgres=# SELECT split_part('A|B|C', '|', 1);
split_part
------------
A
(1 row)
Obtain the last element of the same string (PostgreSQL 14 and later):
postgres=# SELECT split_part('A|B|C', '|', -1);
split_part
------------
C
(1 row)
Not suitable for parsing CSV strings:
postgres=# SELECT split_part('"foo","bar,baz","boo"', ',', 3);
split_part
------------
baz"
(1 row)
References
- PostgreSQL documentation: Other String Functions
Useful links
- PostgreSQL SPLIT_PART() function - overview from w3resource
- PostgreSQL SPLIT_PART Function - overview from PostgreSQL Tutorial