STRING_SPLIT Ordinals: Order the Rows You Read

STRING_SPLIT ordinals give each token a position in the original string. I still use an explicit ORDER BY when the consumer needs those tokens in that sequence.

A position does not order a result

A comma-separated value can describe an ordered list. Turning it into rows changes how that order is represented. The ordinal column carries the position as data. The final SELECT still needs to request the presentation order.

Without ORDER BY, the output order is unspecified. Passing a constant third argument of one enables ordinal output. That column uses bigint and begins at one. The ordinal option applies to SQL Server 2022 and later.

I wouldn’t rely on a result looking ordered during one test. A consumer’s order requirement should be visible in the query. Sorting alphabetically answers a different question from sorting by input position. Repeated values make that difference easier to miss.

Keep the awkward input

The first input contains pear, an empty token, apple and another pear. The duplicate pears are intentional. Their different positions identify separate occurrences. Removing duplicates would change the list instead of restoring its original sequence.

The consecutive commas also have meaning in the demonstration. They produce an empty substring. I keep it in the first result so that the input’s structure stays visible. A later query applies a separate policy for empty tokens.

This script uses three read-only SELECT statements. Its first two split the same explicitly typed string. The third supplies NULL input to show an empty result. No tables, connection options or database settings are modified.

SELECT ordinal, value
FROM STRING_SPLIT(CAST(N'pear,,apple,pear' AS nvarchar(40)), N',', 1)
ORDER BY ordinal;

SELECT ordinal, value
FROM STRING_SPLIT(CAST(N'pear,,apple,pear' AS nvarchar(40)), N',', 1)
WHERE DATALENGTH(value) > 0
ORDER BY ordinal;

SELECT ordinal, value
FROM STRING_SPLIT(CAST(NULL AS nvarchar(40)), N',', 1)
ORDER BY ordinal;

Choose the empty-token policy

The second query excludes tokens whose byte length is zero. That test addresses the empty substring from consecutive delimiters. It doesn’t silently trim the other values. A space-filled token would therefore remain a separate policy decision.

Filtering removes ordinal two from this particular list. The remaining positions are one, three and four. I keep those original positions instead of renumbering them. The gaps describe what was removed from the source list.

I can also justify preserving an empty token. A positional import can use it to represent an omitted field. Dropping that row would shift later values if the consumer relied on sequence. The right policy depends on the list’s contract.

Name the version and type

The third argument must be a constant int or bit value. Don’t build an example that supplies a per-row flag. This script uses the constant one in every call. It also chooses an nvarchar input before splitting.

The value column takes the type and length of the input string. An nvarchar input gives an nvarchar value column. The ordinal column remains bigint. These types are part of the expected result, alongside the visible characters.

The base function requires database compatibility level 130 under the ordinary configuration. The ordinal feature additionally needs a supporting engine. I’d check both before adopting the example on an older database. The script intentionally doesn’t change compatibility settings.

Make order a stated requirement

This is a one-character delimiter example, not a CSV reader. Quoted commas and escaped fields require a different parsing contract. STRING_SPLIT doesn’t make those rules appear by adding ordinals. Keep that boundary clear when replacing application parsing code.

Copy the query to retain every token, its position and the empty third result. Compare complete ordered tuples when testing it. Keep both occurrences of pear with their original ordinals. A row count cannot prove duplicate positions or the empty-token policy.

Try it on a list of your own and watch where each token lands.

An ordinal is not an ordered result, it is the position you can use to request that result.

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