三、结合上边两个函数，像数组一样遍历字符串中的元素（表值函数）
1 create function Func_SplitStr(@SourceSql varchar(8000), @StrSeprate varchar(100))
2 returns @temp table(F1 varchar(100))
3 as
4 begin
5 declare @ch as varchar(100)
6 set @SourceSql=@SourceSql+@StrSeprate
7 while(@SourceSql<>'')
8 begin
9 set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)
10 insert @temp values(@ch)
11 set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
12 end
13 return
14 end
15 go
----调用
select * from dbo.Func_SplitStr('1,2,3,4',',')
--结果：
1
2
3
4
另一种方式（表值函数）：
1 create function Func_SplitStr(@str nvarchar(2000),@split nvarchar(2))
2 returns @t table(AccountCodeID int )
3 as
4 begin
5 declare @tmpAccountCodeID int,@getIndex int
6 set @getIndex=charindex(',',@str)
7 while(@getIndex<>0)
8 begin
9 set @tmpAccountCodeID=convert(int,substring(@str,1,@getIndex-1))
10 insert into @t(AccountCodeID) values (@tmpAccountCodeID)
11 set @str=stuff(@str,1,@getIndex,'')
12 set @getIndex=charindex(',',@str)
13 end
14 insert into @t(AccountCodeID) values (@str)
15 return
16 end
17 go
----调用
select * from dbo.Func_SplitStr('1,2,3,4',',')
--结果：
1
2
3
4
