string

`SPLIT_PART`

Splits a string on a delimiter and returns the Nth part (1-based). Native to Postgres; MySQL emulates via SUBSTRING_INDEX and SQLite requires application-side splitting.

Syntax

SPLIT_PART(string, delimiter, field_number)

Parameters / Modifiers

Parameter	Purpose
SUBSTRING_INDEX(str, delim, n)	MySQL equivalent — returns everything before/after the nth occurrence
n < 0 (Postgres 14+)	Count parts from the end
regexp_split_to_array	Postgres: split on a regex and return the whole array

Examples

SELECT SPLIT_PART('foo.bar.baz', '.', 2);

Postgres: returns 'bar'

SELECT SPLIT_PART('a,b,c,d', ',', -1);

Postgres 14+: returns 'd' (last part)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo.bar.baz','.',2),'.',-1);

MySQL idiom to extract the 2nd part

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

Postgres: extract the domain portion of an email

Dialect notes / Gotcha

SPLIT_PART is Postgres-only — MySQL/SQLite need SUBSTRING_INDEX or expression tricks. Requesting a field number beyond the number of parts returns '' (Postgres), not NULL.

Related functions

SUBSTRING

Extracts a substring by 1-based start position and optional length. All three dialects support the comma-argument form; Postgres also accepts the SQL-standard FROM/FOR clauses and POSIX regex extraction.

POSITION / INSTR

Returns the 1-based index of the first occurrence of substr in string, or 0 if not found. POSITION is SQL-standard (Postgres/MySQL); INSTR is the MySQL/SQLite shorthand.

LEFT / RIGHT

LEFT returns the first n characters, RIGHT returns the last n characters. Available in Postgres and MySQL. SQLite has no LEFT/RIGHT — use SUBSTR(s, 1, n) for LEFT and SUBSTR(s, -n) for RIGHT.35+; a portable fallback is SUBSTRING(str, 1, n) or SUBSTRING(str, LENGTH(str)-n+1).

CONCAT

Joins two or more strings end-to-end and returns a single string. CONCAT is portable across MySQL, Postgres 9.1+, and SQLite 3.44+; the standard || operator works in Postgres and SQLite but not MySQL unless PIPES_AS_CONCAT is set.

LENGTH / CHAR_LENGTH

Returns the number of characters in a string (CHAR_LENGTH) or the number of bytes (LENGTH in MySQL). Postgres and SQLite LENGTH return character count for text, matching CHAR_LENGTH.

← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet