SQL SERVER 自定义函数 split

 1 Create function [dbo].[split]
 2 (
 3   @SourceSql varchar(8000),
 4   @StrSeprate varchar(10)
 5 )
 6 returns @temp table(F1 varchar(100))
 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      insert @temp values(left(@SourceSql,@i-1))
15      set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
16      set @i=charindex(@StrSeprate,@SourceSql)
17    end
18    if @SourceSql<>''
19      insert @temp values(@SourceSql)
20    return
21 end

调用函数查询示例

1 select * from split('1|2|3|4|5','|')