Ordered string split

I'm a bit late to this party, but some long-awaited news is finally here – STRING_SPLIT now returns the ordinal position of string elements in various Azure SQL offerings.

For years before SQL Server 2016 we'd been rolling our own functions to split delimited strings in T-SQL. Here's one approach, using a numbers table:

DECLARE @str NVARCHAR(50) = N'ab,c,def';
 
WITH cte AS (
  SELECT
    n.N + 1 AS Offset
  , LEAD(n.N - 1, 1, LEN(s.S)) OVER (ORDER BY n.N) - n.N AS [Length]
  , s.S
  FROM dbo.Numbers n
    CROSS JOIN (SELECT ',' + @str AS S) s
  WHERE n.N <= LEN(s.S)
  AND SUBSTRING(s.S, n.N, 1) = ','
)
SELECT
  SUBSTRING(S, Offset, [Length]) AS [value]
, ROW_NUMBER() OVER (ORDER BY Offset) AS [ordinal]
FROM cte;

with this result:

Then, along came SQL Server 2016 and STRING_SPLIT:

DECLARE @str NVARCHAR(50) = N'ab,c,def';
 
SELECT * FROM STRING_SPLIT(@str, ',');

Much less code – but look at the output:

No ordinal position! In fact, as the documentation itself admits, the output rows might be in any order 😢.

STRING_SPLIT has an optional third argument! By specifying the value 1 as the third parameter to STRING_SPLIT, you indicate that you'd like also to see the ordinal position in which each string element appears in the original string.

DECLARE @str NVARCHAR(50) = N'ab,c,def';
 
SELECT * FROM STRING_SPLIT(@str, ',', 1);

Here's the result:

Just like the original, pre-2016 version. It might not solve every last one of STRING_SPLIT's issues, but it's a great new feature. It's available right now in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only).