Sql 实现Split

<pre name="code" class="sql">--功能: 实现split的函数  
--用法：select * from dbo.fun_split('ABC:BC:C:D:E::',':')   

--SourceSql  要分隔的字符串

--<span style="font-family: Arial, Helvetica, sans-serif;">StrSeprate 分割符号</span>

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE  function [dbo].[fun_split](@SourceSql varchar(max),@StrSeprate varchar(10))  
returns @temp table(SplitValue NVARCHAR(max))  
as   
begin  
    declare @i int  
    set @SourceSql=rtrim(ltrim(@SourceSql))  
    set @i=charindex(@StrSeprate,@SourceSql)  
    while @i>=1  
    begin  
        insert @temp values(left(@SourceSql,@i-1))  
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)  
        set @i=charindex(@StrSeprate,@SourceSql)  
    end  
    if @SourceSql<>'\'  
    insert @temp values(@SourceSql)  
    return   
end  
GO