sqlindex

Strings

Get the Nth part of a delimited string

3 min · updated September 2, 2026

“Get the month out of '2024-06-15'” — the second field, split on -. Most dialects have a dedicated function; the indexing convention (1-based vs 0-based, and how out-of-range is handled) differs.

-- PostgreSQL  (split_part; 1-indexed; returns '' if the field doesn't exist)
SELECT split_part('2024-06-15', '-', 2);   -- '06'
-- PostgreSQL 16+ also accepts a negative n to count from the right: split_part(s, '-', -1)

-- Snowflake  (SPLIT_PART; 1-indexed; negative n counts from the right; returns '' out of range)
SELECT SPLIT_PART('2024-06-15', '-', 2);   -- '06'

-- DuckDB  (split_part; 1-indexed, same convention as PostgreSQL/Snowflake)
SELECT split_part('2024-06-15', '-', 2);   -- '06'

-- BigQuery  (SPLIT returns an array; index it, 0-indexed — SAFE_OFFSET avoids an error out of range)
SELECT SPLIT('2024-06-15', '-')[SAFE_OFFSET(1)];   -- '06'

MySQL has no split_part. SUBSTRING_INDEX counts delimiters, not fields, so getting a specific field takes two nested calls — take the first N fields from the left, then the last field of that result:

-- MySQL  (nested SUBSTRING_INDEX: inner call keeps the first 2 fields, outer call keeps the last of those)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('2024-06-15', '-', 2), '-', -1);   -- '06'

SQLite has no built-in split function at all (no split_part, no SUBSTRING_INDEX, no array type):

-- SQLite: use the recursive-CTE split from the split-string-to-rows recipe,
-- then filter for row N with a ROW_NUMBER() — or handle it in the application layer.
-- See: /guides/split-string-to-rows/

Function names look interchangeable — the indexing rules aren’t. split_part/SPLIT_PART (PostgreSQL, Snowflake, DuckDB) are 1-indexed, so field 1 is the first part. BigQuery’s SPLIT instead returns a plain array, which BigQuery indexes 0-indexed, so the second field is [OFFSET(1)] — mixing the two conventions up silently shifts every result by one field. Out-of-range behavior also differs: split_part/SPLIT_PART return an empty string, while array[OFFSET(i)] errors on an out-of-range index (use SAFE_OFFSET(i) — returns NULL instead — unless you want the error). MySQL’s SUBSTRING_INDEX is a different tool entirely (delimiter-counting, not field-indexing), and SQLite has no built-in for this at all.

← All recipes