sql 分割字符串split

--方法1：
create function splitstr(@str varchar(8000),@seperator varchar(10))
returns @t table (strs varchar(100))
as
begin
declare @tb varchar(20)
while(len(@str)>0)
begin
set @tb=substring(@str,1,case when patindex('%'+@seperator+'%',@str)=0 then 200 else patindex('%'+@seperator+'%',@str)-1 end)
insert into @t select @tb
set @str=substring(@str,case when patindex('%'+@seperator+'%',@str)=0 then 500 else patindex('%'+@seperator+'%',@str)+1 end,2000)
end
return
end

--方法2：

create function split(@strs varchar(8000),@seprator varchar(10)) returns @t table(tname varchar(30))
as
begin

declare @tb varchar(1000)
while(patindex('%,%',@strs))>0
begin
set @tb=substring(@strs,1,patindex('%'+@seprator+'%',@strs)-1)
insert @t select @tb
set @strs=substring(@strs,patindex('%'+@seprator+'%',@strs)+1,2000)
end
insert into @t select @strs
return
end

select * from dbo.split('美国,中国，英国',',')