sql server 函数切割字符串My_split

--实现split功能的函数 （'a,b,c,d'）转为记录
--说明：@aString，字符串，如“27,28,29”；@pattern，分隔标志，如“,”
-- 例：select * from a where ID in (select Myvalues FROM my_split(@ID, ','))
-- 例：select Myvalues from dbo.My_split('a,b,c,d',',')

create function [dbo].[My_split](@aString varchar(8000),@pattern varchar(10))
returns @temp table([Sid] [int] IDENTITY (1,1) NOT NULL ,Myvalues varchar(100))
as
begin
declare @i int
set @aString=rtrim(ltrim(@aString))
set @i=charindex(@pattern,@aString)
while @i>=1
begin
insert @temp values(left(@aString,@i-1))
set @aString=substring(@aString,@i+1,len(@aString)-@i)
set @i=charindex(@pattern,@aString)
end
if @aString<>''
insert @temp values(@aString)
return
end