SQL 实现Split 功能

create function f_split(@SourceSql varchar(max),@StrSeprate char(1))
returns @temp table(a varchar(100))
--实现split功能 的函数
as
begin
    --declare @temp as table(a varchar(100))
--declare @SourceSql as nvarchar(max)
--declare @StrSeprate as nvarchar(10)
--set @SourceSql='ABC:BC:C:D:E'
--set @StrSeprate=':'
declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        --print substring(@SourceSql,@i+1,1)
        if (substring(@SourceSql,@i+1,1)=@StrSeprate)
        begin
            set @i=charindex(@StrSeprate,@SourceSql,@i+1)
            continue
        end
        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

------------------------

 

select * from dbo.f_split('ABC::BC:C:D::E',':')

 

结果:

a

----

ABC:
BC
C
D:
E

 

以上SQL修改自 阿良.NET