sqlsever创建分隔函数(先按分号分割在按逗号分割成一张表)

--"1,2,3;4,5,6"  逗号分隔的数字分别代表不同的id
--//先按分号分割在按逗号分割成一张表
create function [dbo].[split](@t varchar(8000),@s1 varchar(10),@s2 varchar(10))
returns @retab table(UserId int,RoleId int,ServerId int)
as 
begin 
    declare @i int,@j int,@k int;
    
    set @k=1
    while(1=1)
    begin
	    --分号位置
        select @i = charindex(@s1,@t);
        if(@i>0)
        begin
		--逗号位置
            select @j = charindex(@s2,@t);
            insert @retab select substring(@t,1,@j-1),SUBSTRING(@t,@j+1,CHARINDEX(',',@t,CHARINDEX(',',@t)+1)-@j-1),substring(@t,CHARINDEX(',',@t,CHARINDEX(',',@t)+1)+1,@i-CHARINDEX(',',@t,CHARINDEX(',',@t)+1)-1);
            select @t = substring(@t,@i+1,len(@t))
        end
        else
        begin
            select @j = charindex(@s2,@t);
            insert @retab select substring(@t,1,@j-1),SUBSTRING(@t,@j+1,CHARINDEX(',',@t,CHARINDEX(',',@t)+1)-@j-1),substring(@t,CHARINDEX(',',@t,CHARINDEX(',',@t)+1)+1,len(@t));
            break;
        end
        set @k=@k+1
    end
    return
end  

go
select * from [dbo].[split]('1,2,3;4,5,6',';',',');

运行结果如下: