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
