STRING_SPLIT and Its Ordinal Column

A comma separated list arrives in the right order, but a query result shuffles the tokens. STRING_SPLIT and its ordinal column can preserve position when you ask for it explicitly.

Understand the Basic Split

STRING_SPLIT turns a delimited string into rows. With two arguments it returns a value column. This is useful for simple filtering or parsing, but it does not promise the output order. Relational result sets are unordered unless the outer query has ORDER BY.

I have seen code assume that tokens arrive in input order because the first test returned that way. That is a fragile assumption. A plan change or different input can expose it. If order matters, represent order as data and sort on it.

Ask whether a delimited string is the right input contract at all. A table-valued parameter or staging table is clearer for an application you control. STRING_SPLIT is handy when the string is already the contract or comes from a legacy source.

SELECT value
FROM STRING_SPLIT(N'red,green,blue', N',');

Request the STRING_SPLIT Ordinal Column

On SQL Server 2022 and later, pass a constant 1 as the third argument to request the ordinal column. The ordinal starts at 1 and records the token’s position in the input. The third argument must be a constant, not a variable or column. That detail matters when trying to make one generic query switch the feature on and off.

The ordinal does not force the result rows to be returned in that order. Add ORDER BY ordinal to the outer query. The column gives you the position; ORDER BY gives you the presentation. Those are separate operations.

I check both value and ordinal with repeated tokens. Sorting by value or using CHARINDEX cannot reconstruct positions when the same token appears more than once. The ordinal can.

SELECT value, ordinal
FROM STRING_SPLIT(N'red,green,red', N',', 1)
ORDER BY ordinal;

Keep Empty Tokens Visible Until You Decide

Consecutive delimiters produce an empty token. An input such as red,,blue has a blank middle position. Filter value = N” only if the business rule says empty items should disappear. If position matters, removing one token leaves a gap in the ordinal sequence, which can be useful evidence of bad input.

TRIM can remove spaces around tokens, but decide whether spaces are meaningful for the domain. A product code can be different from a display label. Keep the original value available when cleaning could merge distinct tokens.

I prefer to reject unexpected empty tokens at the boundary rather than silently discard them. A missing item in a position list can shift meaning. A query that returns fewer rows can look cleaner while hiding a malformed request.

SELECT ordinal, value, TRIM(value) AS trimmed_value
FROM STRING_SPLIT(N'red, ,blue', N',', 1)
ORDER BY ordinal;

Join Tokens Back to Data Safely

When tokens are IDs, use TRY_CONVERT before joining to an integer key. Reject nonnumeric tokens explicitly instead of letting an implicit conversion fail halfway through a query. Keep ordinal in the result if the caller expects the original order.

For a small list, STRING_SPLIT can feed a join. For a very large list, a table-valued parameter or staged key table gives the optimizer and application a clearer contract. Avoid concatenating input directly into dynamic SQL. The delimiter function does not make unsafe SQL text safe.

I check duplicate tokens too. If the input lists the same customer twice, a join can duplicate output rows. Decide whether duplicates are meaningful. If they are not, deduplicate by key while documenting that the original positions are discarded.

WITH parsed AS
(
    SELECT ordinal, TRY_CONVERT(int, value) AS CustomerId, value
    FROM STRING_SPLIT(N'10,20,not-an-id', N',', 1)
)
SELECT ordinal, value
FROM parsed
WHERE CustomerId IS NULL
ORDER BY ordinal;

Know Which Versions Have the STRING_SPLIT Ordinal Column

SQL Server versions before the ordinal argument can split values, but STRING_SPLIT does not provide the input position. Do not attach ROW_NUMBER to the unordered output and call it the original order. That only numbers whatever order the plan happened to produce.

On an older version, choose an input that carries its own positions. A JSON array, XML with a tested sequence method, or a table-valued parameter all work. Another option is to upgrade when the broader platform plan allows it. The right choice depends on your input contract.

I look for code that orders by value after splitting. That sorts alphabetically or numerically, not by input position. It can be correct for a set of filters, but wrong for a workflow sequence. Name the requirement first.

Use the STRING_SPLIT Ordinal Column for Positional Work

Ordinal is useful for ordered tags, a sequence of workflow steps, or a display list. It is less important when the string represents an unordered set of IDs. Do not add a positional dependency to data that has no meaningful position. A set remains a set.

A simple test should include repeated values, empty values, and leading or trailing delimiters. Check the business rule for each. The function returns tokens; it does not validate the semantics of the string. A parser that accepts malformed input without complaint makes later joins harder to explain.

I also test the final query’s ORDER BY, not just the split. An intermediate sort in a subquery does not guarantee the final presentation. Sort at the outermost result that the consumer reads.

SELECT ordinal, value
FROM STRING_SPLIT(N'first,second,third', N',', 1)
WHERE value <> N''
ORDER BY ordinal;

Keep Parsing Out of Large Fact Scans

Splitting a column for every row in a large fact table can be expensive. If the list is stored as a repeated attribute, consider normalizing it into a child table during the load. That makes joins, constraints, and indexing much clearer. STRING_SPLIT is best at the boundary or for modest ad hoc input.

Inspect actual plans and row counts for the intended workload. A single sample string says little about thousands of rows with long lists. Check how many tokens are produced and whether a later join multiplies rows. The output grain should be explicit.

The STRING_SPLIT ordinal column solves a real ordering problem, but only when requested and sorted. Use it when sequence carries meaning. Otherwise keep the query as a simple set operation and avoid promising an order that SQL Server never promised.

Do your tokens represent an ordered sequence or an unordered set of values?

Related reading on this blog: Split Comma Separated Value String in a Column Using STRING_SPLIT and Fix Error: Invalid object name STRING_SPLIT.

The ordinal is not an automatic sort order, it is the position you can sort by.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.