Ad
Get The Rank Of Each String Extracted From Split_string
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 |
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];
Ad
source: stackoverflow.com
Related Questions
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?
Ad