SQL Server自定义函数（二）

作用：传入一个字符串和想分割字符串的分隔符，使用此函数将源串分割开来

create function fn_StrSplit(@str varchar(8000), @Separator varchar(10))RETURNS @re TABLE(FID varchar(100))ASbegindeclare @l int, @i intselect @i = len(@Separator), @l = len(@str);with cte as(select 0 a, 1 b union allselect b, charindex(@Separator, @str, b) + @ifrom cte where b > a)insert into @re select substring(@str,a, case when b > @i then b-a-@i else @l - a + 1 end) valuefrom cte where a >0 option (maxrecursion 4000)RETURNend

结果：