[SOLVED] STRING_SPLIT and ROW_NUMBER()

STRING_SPLIT and ROW_NUMBER() - maintaining order

I have a requirement to split a string based on a delimiter and to pull the values based on their ordinals. As I am on SQL Server 2019, I cannot use String_Split with ordinal enabled. Trying to replicate the same functionality using below STRING_SPLIT and Row_Number() query.

select *, row_number() over(order by (select null)) as seq
from (
    SELECT *
    FROM STRING_SPLIT(CurrentPath, '/') where value <> ''
) t

My question is, when using select null along with order by clause and string_split, will the order of values be deterministic? I need to preserve the order in which values are returned by string_split function. Any pointers?

Solution

Since you are 2019, you could use a bit of JSON.

Here [key] is your sequence

Example

Declare @YourTable Table ([ID] varchar(50),[CurrentPath] varchar(50))  Insert Into @YourTable Values 
 (1,'aa\bb\cc')
,(2,'aa\bb\cc\dd')
 
Select A.ID
      ,B.[key]
      ,B.[value]
 from @YourTable A
 Cross Apply OpenJSON( '["'+replace([CurrentPath],'\','","')+'"]' ) B

Results

ID  key     value
1   0       aa
1   1       bb
1   2       cc
2   0       aa
2   1       bb
2   2       cc
2   3       dd