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

---

Id	DYS385
3	10-19
4	13-16
5	13-18
6	13-19
7	13-17
8	13-18
9	13-18
10	14-19
11	13-19
12	13-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

---

Id	DYS385	VALUE
3	10-19	10
3	10-19	19
4	13-16	13
4	13-16	16
5	13-18	13
5	13-18	18
6	13-19	13
6	13-19	19
7	13-17	13
7	13-17	17

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

Desired results:

---

Id	DYS385	VALUE	RANK
3	10-19	10	1
3	10-19	19	2
4	13-16	13	1
4	13-16	16	2
5	13-18	13	1
5	13-18	18	2
6	13-19	13	1
6	13-19	19	2
7	13-17	13	1
7	13-17	17	2

CodePudding user response：

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

CodePudding user response：

Given this data:

CREATE TABLE dbo.Something(Id int, DYS385 varchar(10));

INSERT dbo.Something(Id, DYS385) VALUES (3 , '10-19'), 
(4 , '13-16'), (5 , '13-18'), (6 , '13-19'),
(7 , '13-17'), (8 , '13-18'), (9 , '13-18'),
(10, '14-19'), (11, '13-19'), (12, '13-18');

Another way to make sure you rank the broken-up strings in the right order could be to use tricks like PARSENAME(), though this can be sensitive to strings lengths and whether a dot is valid within the data:

SELECT s.Id, s.DYS385, value = v.v, [rank] = ROW_NUMBER() OVER 
  (PARTITION BY s.Id ORDER BY c.c DESC)
FROM dbo.Something AS s
CROSS APPLY (VALUES(1),(2)) AS c(c)
CROSS APPLY (VALUES(PARSENAME(REPLACE(s.DYS385,'-','.'),c.c))) AS v(v)
ORDER BY s.Id, [rank];

Output:

Id	DYS385	value	rank
3	10-19	10	1
3	10-19	19	2
4	13-16	13	1
4	13-16	16	2
5	13-18	13	1
5	13-18	18	2
6	13-19	13	1
6	13-19	19	2
7	13-17	13	1
7	13-17	17	2
8	13-18	13	1
8	13-18	18	2
9	13-18	13	1
9	13-18	18	2
10	14-19	14	1
10	14-19	19	2
11	13-19	13	1
11	13-19	19	2
12	13-18	13	1
12	13-18	18	2

• Example db<>fiddle

CodePudding user response：

Split_string has an option to output this rank, called ordinal. Just add an extra parameter with the value of 1:

SELECT *FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

That returns:

value   ordinal
Lorem   1
ipsum   2
dolor   3
sit 4
amet.   5

In your case the query would be:

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

CodePudding user response：

We can also make recursive split_string function which returns the rank of each item as follows Link dbfiddle

Create function Split_Recursive(@string nvarchar(max),@delimiter as varchar(1)) returns Table
as
return(
with cte as(
select 1 N,
case when charindex(@delimiter,@string,1) =0 then @string else
substring(@string,1,-1 charindex(@delimiter,@string,1)) end [Found],
case when charindex(@delimiter,@string,1)=0 then @string else substring(@string,1 charindex(@delimiter,@string,1) ,len(@string)) 
end [MYSTRING]
union all
select 1 n,
case when charindex(@delimiter,[MYSTRING],1)=0 then [MYSTRING] else
substring([MYSTRING],1,-1 charindex(@delimiter,[MYSTRING],1)) end,substring([MYSTRING],1 charindex(@delimiter,[MYSTRING]),len([MYSTRING])) from cte
where charindex(@delimiter,[MYSTRING],1)>0),
cte2 as (select N,Found from cte 
union select 1 t.N,Mystring from cte OUTER APPLY(select top 1 N from cte order by n desc)t where t.n=cte.n
)
select N Rank,Found from cte2
)

go

select * from dbo.Split_Recursive('12-16','-')

Output

---

Rank	Found
1	12
2	16