MySQL implements a string splitting function similar to SPLIT

The following function implements a string like array 1.

1, use temporary tables as arrays

 
create function f_split(@c varchar(2000),@split varchar(2)) 
returns @t table(col varchar(20)) 
as 
begin 
while(charindex(@split,@c)<>0) 
begin 
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) 
set @c = stuff(@c,1,charindex(@split,@c),'') 
end 
insert @t(col) values (@c) 
return 
end 
go 
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',') 
drop function f_split 
col 
-------------------- 
dfkd 
dfdkdf 
dfdkf 
dffjk 
 (the number of rows affected is  4  Line)  

2. Divide the string according to the specified symbol, and return the number of elements after the segmentation. The method is very simple, that is, to see how many separator symbols exist in the string, and then add 1, is the required result.

 
CREATE function Get_StrArrayLength 
( 
@str varchar(1024), -- The string to be split  
@split varchar(10) -- separator  
) 
returns int 
as 
begin 
declare @location int 
declare @start int 
declare @length int 
set @str=ltrim(rtrim(@str)) 
set @location=charindex(@split,@str) 
set @length=1 
while @location<>0 
begin 
set @start=@location+1 
set @location=charindex(@split,@str,@start) 
set @length=@length+1 
end 
return @length 
end 

Call example: select dbo.Get_StrArrayLength ('78,1,2,3',',')
Return value: 4

3. Split the string by the specified symbol, and return the number of elements of the specified index after the split, as convenient as array 1

 
CREATE function Get_StrArrayStrOfIndex 
( 
@str varchar(1024), -- The string to be split  
@split varchar(10), -- separator  
@index int -- I'm going to take the first element  
) 
returns varchar(1024) 
as 
begin 
declare @location int 
declare @start int 
declare @next int 
declare @seed int 
set @str=ltrim(rtrim(@str)) 
set @start=1 
set @next=1 
set @seed=len(@split) 
set @location=charindex(@split,@str) 
while @location<>0 and @index>@next 
begin 
set @start=@location+@seed 
set @location=charindex(@split,@str,@start) 
set @next=@next+1 
end 
if @location =0 select @location =len(@str)+1 
-- There are two situations: 1 , string does not exist separator  2 , there is a separator in the string, popup while After the loop, @location for 0 , which defaults to the following of the string 1 A separator.  
return substring(@str,@start,@location-@start) 
end 

Call example: select dbo.Get_StrArrayStrOfIndex ('8,9,4',',' 2)
Return value: 9

4. Combine the above two functions to traverse the elements of the string like array 1

 
declare @str varchar(50) 
set @str='1,2,3,4,5' 
declare @next int 
set @next=1 
while @next<=dbo.Get_StrArrayLength(@str,',') 
begin 
print dbo.Get_StrArrayStrOfIndex(@str,',',@next) 
set @next=@next+1 
end 

Call result:
1
2
3
4
5