SPLIT_PART
MySQL equivalent:
Splits a string by a delimiter and returns the part with the given number.
PostgreSQL 17.9
SPLIT_PART(string, delimiter, part_number)- string
- Source stringtext
- delimiter
- Delimiter, can be several characters longtext
- part_number
- Part number from 1; a negative one counts from the endinteger
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.9
SELECT SPLIT_PART('a|b|c', '|', 2);Last item with a negative number
PostgreSQL 17.9
SELECT SPLIT_PART('a|b|c', '|', -1);Last name from the full name
PostgreSQL 17.9
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.