PostgreSQL split_part() Function

The PostgreSQL split_part() function splits a specified string using the specified delimiter and returns the specified part.

split_part() Syntax

This is the syntax of the PostgreSQL split_part() function:

split_part(string, delimiter, n)

Parameters

string

Required. The string to split.

delimiter

Required. The delimiter.

n

Required. An integer specifying the index (counting from 1) of parts to return.

Return value

The PostgreSQL split_part() function splits the string string using the delimiter delimiter and returns the n-th part. If n is negative, the last -nth part is returned.

split_part() Examples

This example shows how to use the split_part() function to return the third part of a string splited by the delimiter:

SELECT split_part('ab,cd,ef,gh', ',', 3);

 split_part
------------
 ef

Here, split_part() works in the following steps:

  1. Split the string 'ab,cd,ef,gh' using the delimiter ',', the result is an array {ab, cd, ef, gh}.
  2. Returns the 3rd element of {ab, cd, ef, gh}, that is ef.

You can also get the second part from last, for example:

SELECT split_part('ab,cd,ef,gh', ',', -2);

 split_part
------------
 ef

Here, split_part() works in the following steps:

  1. Split the string 'ab,cd,ef,gh' using the delimiter ',', the result is an array {ab, cd, ef, gh}.
  2. Since the parameter n is -2, returns the second-from-last element of {ab, cd, ef, gh}, that is ef.