SPLIT_PART
MySQL equivalent:
Splits a string by a delimiter and returns the part with the given number.
PostgreSQL 17.5
SPLIT_PART(string, delimiter, part_number)Parameters
- stringtext
- Source string
- delimitertext
- Delimiter, can be several characters long
- part_numberinteger
- Part number, starting from 1
Return value
A part of the string (text); an empty string if there is no part with that number; a negative number counts from the end, and 0 causes an error
Examples
Second item by separator
PostgreSQL 17.5
SELECT SPLIT_PART('a|b|c', '|', 2);Last item with a negative number
PostgreSQL 17.5
SELECT SPLIT_PART('a|b|c', '|', -1);Last name from the full name
PostgreSQL 17.5
SELECT member_name,
SPLIT_PART(member_name, ' ', 2) AS last_name
FROM FamilyMembers;Details
The delimiter is a plain string, not a regular expression: to split by a pattern, use REGEXP_SPLIT_TO_TABLE. With an empty delimiter, the whole string is the first part.