SPLIT_PART in PostgreSQL: Split a String and Take the N-th Field

SPLIT_PART cuts a string on a fixed delimiter and returns a single field by its position. It is the most direct way to pull a domain out of an email, a segment from a URL path, or a code from a SKU, without reaching for regular expressions.

Syntax and a basic example

The signature is simple: SPLIT_PART(string, delimiter, n). The string is split on delimiter, fields are numbered from 1, and the function returns the n-th field as text.

SELECT SPLIT_PART(email, '@', 2) AS domain
FROM users;

A few behaviors worth remembering:

• Fields are numbered from one, not zero. SPLIT_PART('a.b.c', '.', 1) returns a.
• If field n does not exist, you get an empty string '', not NULL.
• If the delimiter never appears, the whole value is treated as field number 1.
• delimiter is a literal, not a regex; a dot means a dot.

Real-world scenarios

User domains and a breakdown by mail provider:

SELECT
    SPLIT_PART(email, '@', 2) AS domain,
    COUNT(*)                  AS users
FROM users
GROUP BY SPLIT_PART(email, '@', 2)
ORDER BY users DESC;

The local part (before @) is simply field number 1:

SELECT id, SPLIT_PART(email, '@', 1) AS local_part
FROM users
WHERE country = 'DE';

Parsing a composite order status such as paid:card:eur:

SELECT
    id,
    SPLIT_PART(status, ':', 1) AS payment_state,
    SPLIT_PART(status, ':', 2) AS method,
    SPLIT_PART(status, ':', 3) AS currency
FROM orders;

The top segment of a hierarchical department code like eng/backend/payments:

SELECT name, SPLIT_PART(dept, '/', 1) AS top_level_dept
FROM employees;

Negative index in PostgreSQL 14+

Starting with PostgreSQL 14, the third argument can be negative and counts from the end. That is a lifesaver when the number of segments varies but you specifically want the last one.

-- PostgreSQL 14+: last path segment
SELECT SPLIT_PART('eng/backend/payments', '/', -1);  -- 'payments'

-- second to last
SELECT SPLIT_PART('eng/backend/payments', '/', -2);  -- 'backend'

Gotcha: before version 14 a negative index raises an error instead of returning an empty string. If your code ships to PostgreSQL 13 or earlier, emulate "last field" with reverse() or regexp_replace(path, '.*/', ''). Always confirm the server version before relying on negative counting.

MySQL: SUBSTRING_INDEX

MySQL has no SPLIT_PART, but it offers SUBSTRING_INDEX(str, delim, count). The logic differs: a positive count takes everything before the N-th delimiter from the left, a negative count from the right. To isolate a single field you nest two calls.

-- MySQL: domain from an email (everything after the first '@')
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

-- MySQL: exactly the second field of 'a.b.c.d'
-- take the first two fields, then the last of those
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a.b.c.d', '.', 2), '.', -1);  -- 'b'

For two-field strings (like local@domain) a single SUBSTRING_INDEX(..., -1) is enough. For an arbitrary N you need the two-call sandwich.

ClickHouse: splitByChar and arrays

ClickHouse thinks in arrays: splitByChar(delim, str) returns an array of fields, from which you index the element you want (numbered from 1). For a multi-character delimiter there is splitByString.

-- ClickHouse: domain from an email
SELECT splitByChar('@', email)[2] AS domain
FROM users;

-- ClickHouse: last segment via arrayElement with a negative index
SELECT arrayElement(splitByChar('/', 'eng/backend/payments'), -1);  -- 'payments'

A quick dialect summary:

• PostgreSQL: SPLIT_PART(s, d, n), fields from 1, negative n from PG14.
• MySQL: SUBSTRING_INDEX, double call to isolate one field.
• ClickHouse: splitByChar/splitByString plus array indexing.

When a string has many delimiters and the logic is richer than "the N-th field," PostgreSQL's regexp_split_to_array or regexp_match fit better. But for clean, predictable parsing, SPLIT_PART remains the shortest and most readable tool you have.