split_part()

Splits string at occurrences of delimiter and returns the n'th field (counting from one), or when n is negative, returns the |n|'th-from-last field.

Signature

split_part ( string text, delimiter text, n integer ) → text

Examples

SELECT split_part('abc~@~def~@~ghi', '~@~', 2);

def

SELECT split_part('abc,def,ghi,jkl', ',', -2);

ghi

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	split_part
MySQL	SUBSTRING_INDEX(x, ',', 2) — cumulative, not the nth field
SQL Server	STRING_SPLIT with ordinal, on SQL Server 2022+
Oracle	REGEXP_SUBSTR(x, '[^,]+', 1, 2)
SQLite	no direct equivalent

MySQL's SUBSTRING_INDEX returns everything up to the nth delimiter rather than the nth field, so the naive translation of split_part(x, ',', 2) is wrong in a way that looks right on single-field input.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.