sqlserver實現分隔字元串

sqlserver 使用函數實現分隔字元串

create function dbo.fn_split
(
    @str_source nvarchar(max),
    @split_char nvarchar(100)
)
returns @temp table
(
    id int primary key identity(1,1),
    val varchar(max)
)
as
begin
    declare @idx int,@split_len int

    set @str_source=rtrim(ltrim(@str_source))
    set @idx=charindex(@split_char,@str_source)
    set @split_len=len(@split_char)

    while(@idx>0)
    begin
        insert into @temp values(left(@str_source,@idx-1))

        set @idx+=@split_len-1
        set @str_source=substring(@str_source,@idx+1,len(@str_source)-@idx)
        set @idx=charindex(@split_char,@str_source)
    end

    if(@str_source!='')
    begin
       insert into @temp values(@str_source)
    end

    return
end