Splitting strings is one of the most common data manipulation tasks in SQL, yet the syntax varies significantly across different database engines. Whether you are parsing CSV data, extracting domains from email addresses, or separating full names into first and last components, the ability to isolate a specific segment—such as the second element—is a fundamental skill. This guide provides a comprehensive breakdown of how to split a string by a delimiter and retrieve the second element in the most popular SQL dialects, including T-SQL (SQL Server), PostgreSQL, MySQL, Oracle, and Snowflake Which is the point..
Understanding the Core Concept
Before diving into vendor-specific syntax, it helps to visualize the logic. Even so, a delimited string follows a pattern: Element1<Delimiter>Element2<Delimiter>Element3... Day to day, . The goal is to ignore the first element, skip the first delimiter, and capture everything up to the second delimiter (or the end of the string) Worth keeping that in mind. That alone is useful..
Most modern databases offer a SPLIT_PART, STRING_SPLIT, or SUBSTRING_INDEX function. Older versions often require creative combinations of CHARINDEX, INSTR, SUBSTRING, and SUBSTR. Performance considerations also differ; set-based functions are generally faster than recursive CTEs or WHILE loops for large datasets.
SQL Server (T-SQL) Approaches
SQL Server has evolved considerably regarding string parsing. The approach you take depends heavily on your compatibility level and version.
Using STRING_SPLIT (SQL Server 2016+)
Introduced in SQL Server 2016, STRING_SPLIT is a table-valued function that splits a string into rows. Even so, it does not guarantee the order of output in versions prior to SQL Server 2022 (unless you enable compatibility level 160 or use the ordinal parameter introduced in 2022).
Syntax (SQL Server 2022 / Azure SQL Database):
DECLARE @InputString VARCHAR(MAX) = 'Apple,Banana,Cherry,Date';
DECLARE @Delimiter CHAR(1) = ',';
SELECT value AS SecondElement
FROM STRING_SPLIT(@InputString, @Delimiter, 1) -- The '1' enables the ordinal column
WHERE ordinal = 2;
Syntax (SQL Server 2016 - 2019):
Because ordinal support is missing, you must rely on a workaround, often using ROW_NUMBER() over a derived table, though physical order isn't strictly guaranteed by the engine. For production systems on older versions, the PARSENAME hack or XML method (below) is safer for positional accuracy.
The PARSENAME Hack (Quick & Dirty)
A classic T-SQL trick involves replacing the delimiter with a dot (.Day to day, ) and using PARSENAME, which is designed to parse four-part object names (Server. Now, database. That said, schema. Object). It reads right-to-left.
DECLARE @InputString VARCHAR(100) = 'Apple,Banana,Cherry';
-- Replace comma with dot
-- PARSENAME(part 3) gets the 2nd element from left in a 3-part string
SELECT PARSENAME(REPLACE(@InputString, ',', '.'), 3) AS SecondElement;
-- Result: Banana
Limitations: It only works for up to 4 elements. Elements cannot contain dots. Strings longer than 128 characters per segment may truncate.
Using XML and XQuery (Pre-2016 / Complex Delimiters)
For multi-character delimiters or older versions, converting the string to XML is solid.
DECLARE @InputString VARCHAR(MAX) = 'Apple||Banana||Cherry';
DECLARE @Delimiter VARCHAR(10) = '||';
DECLARE @XML XML = CAST(' ' AS XML);
SELECT @XML.value('/r[2]/text()[1]', 'VARCHAR(MAX)') AS SecondElement;
This handles multi-character delimiters gracefully and preserves order explicitly via the [2] index.
PostgreSQL: The Power of SPLIT_PART
PostgreSQL offers the most intuitive native function for this specific task: SPLIT_PART(string, delimiter, field_number). It is 1-indexed, stable, and handles edge cases (like missing delimiters) by returning an empty string rather than an error Turns out it matters..
SELECT SPLIT_PART('Apple,Banana,Cherry', ',', 2) AS SecondElement;
-- Returns: Banana
Handling Edge Cases in Postgres
If the delimiter is missing:
SELECT SPLIT_PART('Apple', ',', 2); -- Returns empty string ''
If you need NULL instead of an empty string for missing elements, wrap it in NULLIF:
SELECT NULLIF(SPLIT_PART('Apple', ',', 2), '') AS SecondElement; -- Returns NULL
Regex Alternative: REGEXP_SPLIT_TO_ARRAY
For complex splitting logic (e.g., multiple delimiters), convert to an array and access the index (arrays are 1-indexed in SQL standard, but Postgres arrays are 1-indexed by default) And that's really what it comes down to. That's the whole idea..
SELECT (REGEXP_SPLIT_TO_ARRAY('Apple,Banana;Cherry', '[,;]'))[2] AS SecondElement;
-- Returns: Banana
MySQL: SUBSTRING_INDEX Simplicity
MySQL provides SUBSTRING_INDEX(str, delim, count). This function returns the substring before a specified number of occurrences of the delimiter.
Because of that, * Positive count: Returns everything to the left of the Nth delimiter. * Negative count: Returns everything to the right of the Nth delimiter That's the whole idea..
To get the second element, the logic is: "Get everything left of the 2nd delimiter, then get everything right of the 1st delimiter."
SET @str = 'Apple,Banana,Cherry';
SET @delim = ',';
-- Step 1: Get 'Apple,Banana' (Left of 2nd comma)
-- Step 2: Get 'Banana' (Right of 1st comma in that result)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str, @delim, 2), @delim, -1) AS SecondElement;
MySQL 8.0+ JSON Table Approach
For more complex parsing or if you need to split into rows first, MySQL 8.0 allows JSON functions Practical, not theoretical..
SELECT jt.val
FROM JSON_TABLE(
CONCAT('["', REPLACE(@str, @delim, '","'), '"]'),
"$[*]" COLUMNS (val VARCHAR(100) PATH "$", ordinal FOR ORDINALITY)
) AS jt
WHERE jt.ordinal = 2;
Oracle Database: REGEXP_SUBSTR and SUBSTR/INSTR
Oracle lacks a direct SPLIT_PART equivalent prior to version 21c (which introduced SPLIT_STRING table function), so REGEXP_SUBSTR is the standard modern approach Less friction, more output..
Using REGEXP_SUBSTR (Oracle 10g+)
The 6th parameter (occurrence) in REGEXP_SUBSTR makes this trivial. The pattern [^,]+ matches one or more non-comma characters.
SELECT REGEXP_SUBSTR('Apple,Banana,Cherry', '[^,]+', 1, 2) AS SecondElement
FROM dual;
-- Parameters: Source, Pattern, Start Position, Occurrence (2nd)