The PostgreSQL split_part() function splits a string based on a specified delimiter and then returns the specified part from the split string.

The function accepts three arguments; the string, the delimiter, and the part that we want to return.

Example

Here’s an example to demonstrate:

SELECT split_part('Cat,Dog,Horse', ',', 2);

Result:

Dog

In this case the string is a comma-separated list of animals. Therefore I used a comma as the second argument. I wanted the second item and so I specified 2.

Multiple Characters for the Delimiter

If the delimiter consists of multiple characters we can include all of those characters in the second argument:

SELECT split_part('Cat, Dog, Horse', ', ', 3);

Result:

Horse

In this case the delimiter was a comma, but there was also a space between the comma and the item.

Another way of dealing with that scenario is to use the trim() function:

SELECT trim(split_part('Cat, Dog, Horse', ',', 3));

Result:

Horse

Here are some more examples:

SELECT 
    split_part('Cat+Dog+Horse', '+', 2) AS "1",
    split_part('Cat~|~Dog~|~Horse', '~|~', 2) AS "2",
    split_part('Cat and a Dog and a Horse', ' and a ', 2) AS "3";

Result:

  1  |  2  |  3  
-----+-----+-----
 Dog | Dog | Dog

When the Delimiter Doesn’t Match

If we specify a delimiter that isn’t in the string, then the string isn’t split and there’s only one part to the string. Therefore we can only reference it with an index of 1:

SELECT split_part('Cat+Dog+Horse', '=', 1);

Result:

Cat+Dog+Horse

Here’s what happens if I try to reference it by another index:

SELECT split_part('Cat+Dog+Horse', '=', 2);

Result:

 split_part 
------------
 
(1 row)

Nothing but an empty string is returned.

Negative Count

If the third argument is a negative value, it will start counting back from the end of the string:

SELECT 
    split_part('Cat+Dog+Horse', '+', -1) AS "-1",
    split_part('Cat+Dog+Horse', '+', -2) AS "-2",
    split_part('Cat+Dog+Horse', '+', -3) AS "-3";

Result:

  -1   | -2  | -3  
-------+-----+-----
 Horse | Dog | Cat