SQLServer拆分字符串到临时表

 1 Create function [dbo].[split]
 2 (
 3 @SourceSql varchar(max),
 4 @StrSeprate varchar(10)
 5 )
 6 returns @temp table(line varchar(max))
 7 as
 8 begin
 9     declare @i int
10     set @SourceSql = rtrim(ltrim(@SourceSql))
11     set @i = charindex(@StrSeprate,@SourceSql)
12     while @i >= 1
13     begin
14         if len(left(@SourceSql,@i-1))>0
15         begin
16             insert @temp values(left(@SourceSql,@i-1))
17         end
18         set @SourceSql=substring(@SourceSql,@i+len(@StrSeprate),len(@SourceSql)-@i)
19         set @i=charindex(@StrSeprate,@SourceSql)
20     end
21     if @SourceSql <> ‘‘
22         insert @temp values(@SourceSql)
23     return
24 end
25  
26  
27 -----测试
28 GO
29 Select * From dbo.split(‘1,3,2,4,l‘,‘,‘)