将逗号分隔的string转化成array(table set)

/*
    split a comma-delimited-string into a result set (array)
*/
if object_id('split_string_identity') is not null
    drop function split_string_identity
go

create function split_string_identity(@str nvarchar(4000))
    returns @ret table(value nvarchar(512))
as
begin
    declare @position table(i int identity, b bit)
    insert @position(b) select top 1000 0 from master..sysobjects a
    delete from @position where substring(@str, i, 1) <> ','
    insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @position a join @position b on a.i < b.i group by a.i
    return
end
go

select * from split_string_identity(',split,string,with,identity,table,')
drop function split_string_identity

-----------------------------------------------------
if object_id('split_string_position') is not null
    drop function split_string_position
go

create function split_string_position(@str nvarchar(4000))
    returns @ret table(value nvarchar(512))
as
begin
    declare @pos table(i int)
    declare @i int
    set @i = 0
    while(charindex(',', @str, @i+1) > 0)
    begin
        set @i = charindex(',', @str, @i+1)
        insert @pos select @i
    end
    insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @pos a join @pos b on a.i < b.i group by a.i
    return
end
go

select * from split_string_position(',find,comma,position,to,split,string,')
drop function split_string_position

-----------------------------------------------------
if object_id('split_string_position_v2') is not null
    drop function split_string_position_v2
go

create function split_string_position_v2(@str nvarchar(4000))
    returns @ret table(value nvarchar(512))
as
begin
    declare @pos table(i int)
    insert @pos select 0
    while(charindex(',', @str, (select max(i)+1 from @pos)) > 0)
        insert @pos select charindex(',', @str, (select max(i)+1 from @pos))
    delete from @pos where i = 0
    insert @ret select value=substring(@str, a.i+1, min(b.i)-(a.i+1)) from @pos a join @pos b on a.i < b.i group by a.i
    return
end
go

select * from split_string_position_v2(',find,comma,position,to,split,string,version2,')
drop function split_string_position_v2