split_part()

A function for splitting a string on a delimiter and returning one of the fields

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() 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

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)

Categories

System function

See also

regexp_split_to_array()|], ´╗┐regexp_split_to_table(), ]string_to_array(), ´╗┐string_to_table()