I know it has a lot of answers, but I want to write my version of split function like others and like string_split SQL Server 2016 native function.
create function [dbo].[Split]
(
@Value nvarchar(max),
@Delimiter nvarchar(50)
)
returns @tbl table
(
Seq int primary key identity(1, 1),
Value nvarchar(max)
)
as begin
declare @Xml xml = cast('<d>' + replace(@Value, @Delimiter, '</d><d>') + '</d>' as xml);
insert into @tbl
(Value)
select a.split.value('.', 'nvarchar(max)') as Value
from @Xml.nodes('/d') a(split);
return;
end;
- Seq column is primary key to support fast join with other real table or Split function returned table.
- Used XML function to support large data (looping version will slow down significantly when you have large data)
Here's a answer to question.
CREATE TABLE Testdata
(
SomeID INT,
OtherID INT,
String VARCHAR(MAX)
);
INSERT Testdata SELECT 1, 9, '18,20,22';
INSERT Testdata SELECT 2, 8, '17,19';
INSERT Testdata SELECT 3, 7, '13,19,20';
INSERT Testdata SELECT 4, 6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';
select t.SomeID, t.OtherID, s.Value
from Testdata t
cross apply dbo.Split(t.String, ',') s;
--Output
SomeID OtherID Value
1 9 18
1 9 20
1 9 22
2 8 17
2 8 19
3 7 13
3 7 19
3 7 20
4 6
9 11 1
9 11 2
9 11 3
9 11 4
Joining Split with other split
declare @Names nvarchar(max) = 'a,b,c,d';
declare @Codes nvarchar(max) = '10,20,30,40';
select n.Seq, n.Value Name, c.Value Code
from dbo.Split(@Names, ',') n
inner join dbo.Split(@Codes, ',') c on n.Seq = c.Seq;
--Output
Seq Name Code
1 a 10
2 b 20
3 c 30
4 d 40
Split two times
declare @NationLocSex nvarchar(max) = 'Korea,Seoul,1;Vietnam,Kiengiang,0;China,Xian,0';
with rows as
(
select Value
from dbo.Split(@NationLocSex, ';')
)
select rw.Value r, cl.Value c
from rows rw
cross apply dbo.Split(rw.Value, ',') cl;
--Output
r c
Korea,Seoul,1 Korea
Korea,Seoul,1 Seoul
Korea,Seoul,1 1
Vietnam,Kiengiang,0 Vietnam
Vietnam,Kiengiang,0 Kiengiang
Vietnam,Kiengiang,0 0
China,Xian,0 China
China,Xian,0 Xian
China,Xian,0 0
Split to columns
declare @Numbers nvarchar(50) = 'First,Second,Third';
with t as
(
select case when Seq = 1 then Value end f1,
case when Seq = 2 then Value end f2,
case when Seq = 3 then Value end f3
from dbo.Split(@Numbers, ',')
)
select min(f1) f1, min(f2) f2, min(f3) f3
from t;
--Output
f1 f2 f3
First Second Third
Generate rows by range
declare @Ranges nvarchar(50) = '1-2,4-6';
declare @Numbers table (Num int);
insert into @Numbers values (1),(2),(3),(4),(5),(6),(7),(8);
with t as
(
select r.Seq, r.Value,
min(case when ft.Seq = 1 then ft.Value end) ValueFrom,
min(case when ft.Seq = 2 then ft.Value end) ValueTo
from dbo.Split(@Ranges, ',') r
cross apply dbo.Split(r.Value, '-') ft
group by r.Seq, r.Value
)
select t.Seq, t.Value, t.ValueFrom, t.ValueTo, n.Num
from t
inner join @Numbers n on n.Num between t.ValueFrom and t.ValueTo;
--Output
Seq Value ValueFrom ValueTo Num
1 1-2 1 2 1
1 1-2 1 2 2
2 4-6 4 6 4
2 4-6 4 6 5
2 4-6 4 6 6
