sql的split()函数

ALTER  function [dbo].[StrToList_Test](@Str varchar(max), @fg NVARCHAR())

returns  @table table(

    value nvarchar(max)

)

as

begin

declare @tempStr nvarchar(max),@len INT = LEN(@fg);

--去除前后分割符

while substring(@Str,,@len)=@fg

begin

    set @Str=substring(@Str,@len+,len(@Str))

end

while RIGHT(@Str,@len)=@fg

begin

    set @Str=substring(@Str,,len(@Str)-@len)

end

if(len(@Str)>)

begin

    while(charindex(@fg,@Str)>)

    begin

        set @tempStr=substring(@Str,,charindex(@fg,@Str)-)

        insert into @table(value) values(@tempStr)

        set @Str=substring(@Str,charindex(@fg,@Str)+@len,len(@Str))

    end

    insert into @table(value) values(@Str)  --没有分割符保存值

end

return

end

调用如：select * from [dbo].[StrToList_Test]('ab||cd||ef||ghi||jg','||')