Ad

Get The Rank Of Each String Extracted From Split_string

I have a TABLE with STR DNA DATA the following Table [DYS]


IdDYS385
310-19
413-16
513-18
613-19
713-17
813-18
913-18
1014-19
1113-19
1213-18

I am USING the following script to split the values of [DYS385]

select top 10 id,[DYS385],t.Value 
from dys 
OUTER APPLY(select * from string_split([DYS385],'-')) t
where dys385 is not null

Output


IdDYS385VALUE
310-1910
310-1919
413-1613
413-1616
513-1813
513-1818
613-1913
613-1919
713-1713
713-1717

I want to get for each Value, the Rank example 10-19 => 10: Rank 1, 19: Rank 2

Desired results:


IdDYS385VALUERANK
310-19101
310-19192
413-16131
413-16162
513-18131
513-18182
613-19131
613-19192
713-17131
713-17172
Ad

Answer

Use an alternative string-split method, such as XML or Json that can return an ordinal position, such as:

create function dbo.SplitString(@string varchar(1000), @Delimiter varchar(10))
returns table
as
   return(  
      select j.[value], 1 + Convert(tinyint,j.[key]) Seq 
      from OpenJson(Concat('["',replace(@string,@delimiter, '","'),'"]')) j
   );


select value, Seq as [Rank]
from dbo.SplitString('10-19','-')
order by [Rank];

Example fiddle

Ad
source: stackoverflow.com
Ad