SPLIT_PART

Split the input string by a literal delimiter and return the nth part.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

SPLIT_PART(str, delimiter, n)

Description

## Overview Splits the input string by the literal delimiter and returns the part at the specified 1-based index. Unlike SPLIT (which returns the full array), SPLIT_PART avoids allocating the array when you only need one part. The negative-index idiom (-1 for last, -2 for second-to-last) is handy for extracting the final segment of a dotted identifier, URL, or file name. For pattern-based splitting, use REGEXP_SPLIT_TO_ARRAY followed by element access; SPLIT_PART treats the delimiter as a literal. ## Behavior - Returns NULL when any argument is NULL. - Returns an empty string when the requested index is outside the available number of parts. - Positive indices count from the left (1-based). Index 1 is the first part. - Negative indices count from the right: -1 is the last part, -2 is the second-to-last, and so on. - Returns the entire input when the delimiter is not found and the requested index is 1 or -1. - The delimiter is literal; regex metacharacters are not interpreted. - Operates on Unicode code points; multi-byte characters are preserved. ## Compatibility - Matches the common SQL SPLIT_PART semantics including the negative-index extension.

Parameters

Name	Type	Description
`str`		Specifies the input string to split.
`delimiter`		Specifies the literal delimiter used to split the input. May be multiple characters.
`n`		Specifies the 1-based index of the part to return. Negative values count from the last part (so -1 is the last part).

Examples

-- Second part
SELECT SPLIT_PART('a,b,c', ',', 2);  -- 'b'

-- Extract email domain
SELECT SPLIT_PART('user@example.com', '@', 2);  -- 'example.com'

-- Index beyond end returns empty string
SELECT SPLIT_PART('a,b', ',', 5);  -- ''

-- Negative index: last part
SELECT SPLIT_PART('a,b,c', ',', -1);  -- 'c'

-- Multi-character delimiter
SELECT SPLIT_PART('a::b::c', '::', 2);  -- 'b'

-- NULL propagates
SELECT SPLIT_PART(CAST(NULL AS VARCHAR), ',', 1);  -- NULL

Pitfalls

Indices are 1-based, not 0-based. SPLIT_PART(s, ',', 0) returns an empty string, not the first part.
When the delimiter is not found, SPLIT_PART(s, d, 1) returns the whole input, but SPLIT_PART(s, d, 2) returns an empty string, not NULL. Distinguish 'missing' from 'empty' explicitly if that matters.
Multi-character delimiters match exactly. SPLIT_PART('a.b.c', '.', 1) returns 'a'; SPLIT_PART('a.b.c', '..', 1) returns 'a.b.c'.
Negative indices count parts from the end, not characters. Off-by-one confusion between -1 (last part) and -N (first part of N) is a common bug.