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 (string
TEXT
,delimiter
TEXT
,n
INTEGER
) →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:
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