sqlcmd.net validated sql reference

Command guide

intermediate string-processing MySQL MariaDB SQL Server PostgreSQL SQLite

Extract the Nth Field From a Delimited String

Pull out a specific token from a dot- or delimiter-separated string using `SPLIT_PART` (PostgreSQL), `SUBSTRING_INDEX` (MySQL/MariaDB), `PARSENAME` (SQL Server), or nested `SUBSTR`/`INSTR` (SQLite).

Created Last updated 5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Extract the middle segment (region) from a three-part location code

For 'USA.California.SFO', the target is the second (middle) field. In MySQL and MariaDB, SUBSTRING_INDEX(path, '.', 2) returns 'USA.California' (everything up to the second dot); the outer SUBSTRING_INDEX(..., '.', -1) then returns everything after the last dot in that intermediate string, yielding 'California'. In SQL Server, PARSENAME indexes from the right: for a three-part string, position 2 is the middle element. In PostgreSQL, SPLIT_PART(path, '.', 2) returns the second field directly with 1-based left-to-right indexing. In SQLite, INSTR(path, '.') locates the first dot (position 4 in 'USA.California.SFO'); SUBSTR(path, 5) gives 'California.SFO'; a second INSTR finds the next dot (position 11) and SUBSTR(..., 1, 10) extracts 'California'. To extract the first or last field, simpler expressions suffice: SPLIT_PART(path, '.', 1) or SUBSTRING_INDEX(path, '.', 1) for the first; SPLIT_PART(path, '.', -1) or SUBSTRING_INDEX(path, '.', -1) for the last.

MySQL MariaDB
Engine-specific syntax
Source table data
Setup
CREATE TABLE locations (id INT, path VARCHAR(100));

INSERT INTO
  locations
VALUES
  (1, 'USA.California.SFO'),
  (2, 'GBR.England.LHR');
Validated query
SQL
SELECT
  id,
  SUBSTRING_INDEX (SUBSTRING_INDEX (path, '.', 2), '.', -1) AS region
FROM
  locations
ORDER BY
  id;
Expected result
idregion
1California
2England
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE locations (id INT, path VARCHAR(100));

INSERT INTO
  locations
VALUES
  (1, 'USA.California.SFO'),
  (2, 'GBR.England.LHR');
Validated query
SQL
SELECT
  id,
  PARSENAME (path, 2) AS region
FROM
  locations
ORDER BY
  id;
Expected result
idregion
1California
2England
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE locations (id INT, path VARCHAR(100));

INSERT INTO
  locations
VALUES
  (1, 'USA.California.SFO'),
  (2, 'GBR.England.LHR');
Validated query
SQL
SELECT
  id,
  SPLIT_PART (path, '.', 2) AS region
FROM
  locations
ORDER BY
  id;
Expected result
idregion
1California
2England
SQLite
Engine-specific syntax
Source table data
Setup
CREATE TABLE locations (id INT, path TEXT);

INSERT INTO
  locations
VALUES
  (1, 'USA.California.SFO'),
  (2, 'GBR.England.LHR');
Validated query
SQL
SELECT
  id,
  SUBSTR (
    SUBSTR (path, INSTR (path, '.') + 1),
    1,
    INSTR (SUBSTR (path, INSTR (path, '.') + 1), '.') - 1
  ) AS region
FROM
  locations
ORDER BY
  id;
Expected result
idregion
1California
2England

All supported engines return the same rows, but the SQL differs significantly between engines.

Useful when

Where this command helps.

  • parsing a hierarchical region code stored as a single column into country, state, and city fields
  • extracting the major version number from a semantic version string stored in an application config table

Explanation

What the command is doing.

Extracting the Nth element from a delimited string is a common need when data arrives in packed form — version strings like 2.14.1, hierarchical codes like US.CA.SFO, or structured identifiers where each segment has distinct meaning. PostgreSQL provides SPLIT_PART(string, delimiter, n) where n is 1-based from the left (negative indices count from the right in PostgreSQL 14+). MySQL and MariaDB provide SUBSTRING_INDEX(string, delim, n) which returns everything up to the nth occurrence when n is positive; chaining a second call with n = -1 extracts just the nth field. SQL Server's PARSENAME(string, n) parses dot-separated names of up to four parts and uses 1-based indexing from the right, making it convenient for dot-delimited strings when you know the total part count. SQLite has no dedicated function but the combination of SUBSTR and INSTR can extract any field through nested expressions. For more than a handful of fields or for non-dot delimiters, prefer splitting into rows first using engine-specific split functions and then filtering by position.

Tags

stringsplitsubstringdelimiterparse

Related commands

Keep moving through nearby tasks.

Official documentation