split 函数

ALTER function [dbo].[fun_split](@SourceSql nvarchar(max),@StrSeprate varchar(10))
returns @temp table(SplitValue nvarchar(500))
--功能: 实现split的函数
--用法：select * from dbo.fun_split('ABC:BC:C:D:E',':') 
as 
begin
    DECLARE @i INT, @tmpStr VARCHAR(100)
    SET @i = CHARINDEX(@StrSeprate, @SourceSql)
    WHILE @i > 0
        BEGIN
            SET @tmpStr = SUBSTRING(@SourceSql, 1, @i-1)
            SET @SourceSql = STUFF(@SourceSql, 1, @i, '')
            SET @i = CHARINDEX(@StrSeprate, @SourceSql)
            INSERT INTO @temp VALUES(@tmpStr)
        END
    INSERT INTO @temp VALUES(@SourceSql)
    RETURN
end