An SQL split string operation is database-specific. SQL Server, PostgreSQL, MySQL, and Oracle use different functions and return different shapes. To split string in SQL, choose the syntax for your database engine, then define how ordering, empty tokens, and delimiters should behave.

This SQL string split guide returns usable rows or array elements rather than treating one function as portable standard SQL.

SQL split string: SQL Server STRING_SPLIT, ordinals, and empty tokens

SQL Server uses STRING_SPLIT and normally returns one row per token in a value column. SQL Server 2022 and supported Azure versions can also return an ordinal column by passing 1 as the third argument.

Input and query:

DECLARE @s nvarchar(100) = N’red,,blue’;

SELECT s.ordinal, s.value
FROM STRING_SPLIT(@s, N’,’, 1) AS s
ORDER BY s.ordinal;

The result is ordered rows: red, an empty value, and blue. Without the ordinal option, output order is not guaranteed. Consecutive delimiters produce empty substrings; filter them with WHERE s.value <> N” when they are not meaningful. STRING_SPLIT accepts a single-character separator, so use another method for multi-character delimiters.

PostgreSQL: string_to_array and regexp_split_to_table for arrays or rows

PostgreSQL offers a literal-delimiter function and a regular-expression function. string_to_array returns one array, supports multi-character text delimiters, and preserves empty fields between delimiters.

Array input:

SELECT string_to_array(‘red,,blue’, ‘,’) AS pieces;

To turn that array into ordered rows, use unnest with ordinality:

SELECT x.position, x.piece
FROM unnest(string_to_array(‘red,,blue’, ‘,’)) WITH ORDINALITY AS x(piece, position)
ORDER BY x.position;

For a regular-expression delimiter, use regexp_split_to_table:

SELECT x.piece
FROM regexp_split_to_table(‘red||blue||green’, E’\|\|’) AS x(piece);

This returns rows directly. Regex matching determines the split behavior; zero-length matches at the beginning or end are ignored. Use a pattern that matches the delimiter itself when empty values matter.

MySQL: JSON_TABLE or recursive splitting for rows

MySQL 8.0 has no general-purpose native split function. JSON_TABLE provides a compact row-producing pattern by converting the delimited value to a JSON array. The example below supports a multi-character literal delimiter and preserves empty values.

Input and query:

SET @s = ‘red||||blue’;

SET @delimiter = ‘||’;

SELECT j.item_no, j.value
FROM JSON_TABLE(
CONCAT(‘[‘, REPLACE(JSON_QUOTE(@s), @delimiter, ‘”,”‘), ‘]’),
‘$[*]’ COLUMNS (
item_no FOR ORDINALITY,
value VARCHAR(100) PATH ‘$’
)
) AS j
ORDER BY j.item_no;

The returned rows are red, an empty value, and blue. This conversion treats the delimiter literally, not as a regular expression, and assumes the delimiter does not occur inside a value. For more complex parsing rules, a recursive common table expression can repeatedly locate the delimiter with LOCATE or INSTR and return each piece with its position.

Oracle: REGEXP_SUBSTR for ordered rows and regex delimiters

Oracle can extract the nth token with REGEXP_SUBSTR. REGEXP_COUNT supplies the number of rows, making the result ordered and allowing empty tokens to remain visible as NULL.

Input and query:

SELECT LEVEL AS position,
REGEXP_SUBSTR(‘red,,blue’, ‘([^,]*)(,|$)’, 1, LEVEL, NULL, 1) AS piece
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(‘red,,blue’, ‘,’) + 1
ORDER BY position;

The first capture group returns each token, including the empty middle token; Oracle represents an empty string as NULL. REGEXP_SUBSTR accepts regular-expression delimiters, so the capture pattern can be adapted for multi-character separators such as :: or more complex boundary rules. Keep the row-count expression aligned with that delimiter and use ORDER BY position for deterministic output.