====== Ordered string split ======

{{tag> tsql blog}}

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 [[https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15#enable_ordinal|various Azure SQL offerings]].

===== A bit of history =====

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 [[https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1|numbers table]]:

<code tsql>
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;
</code>

with this result:

{{:tsql:string_split2.png?nolink&140|}}

Then, along came SQL Server 2016 and ''STRING_SPLIT'':

<code tsql>
DECLARE @str NVARCHAR(50) = N'ab,c,def';

SELECT * FROM STRING_SPLIT(@str, ',');
</code>

Much less code -- but look at the output:

{{:tsql:string_split1.png?nolink&90|}}

No ordinal position! In fact, as [[https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15#remarks|the documentation]] itself admits, //the output rows might be in any order// 😢.

===== Back to November 2021 =====
''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. 

<code tsql>
DECLARE @str NVARCHAR(50) = N'ab,c,def';

SELECT * FROM STRING_SPLIT(@str, ',', 1);
</code>

Here's the result:

{{:tsql:string_split2.png?nolink&140|}}

Just like the original, pre-2016 version. It might not solve every last one of [[https://sqlperformance.com/2020/03/t-sql-queries/please-help-string-split-improvements|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).