sql server 拆分字符串&查找第n个分割符的位置

拆分字符串.

假如需要将'a,b,c,d,e''字符串按照逗号分开,有以下方式:

1>创建一个拆分函数,之后调用即可

CREATE function dbo.uf_GetItemsInString(@string nvarchar(1000),@spliter char(1)=',')

returns @table table(id int identity(1,1) primary key,items varchar(100) not null)

AS

begin

if patindex('%'+@spliter+'%',@string)=1--以","开始

begin

set @string=substring(@string,2,len(@string))

end

if patindex('%'+@spliter+'%',reverse(@string))=1--以","结束

begin

set @string=reverse(substring(reverse(@string),2,len(@string)))

end

while patindex('%'+@spliter+'%',@string)>0

begin

declare @file varchar(1000)

set @file=substring(@string,0,patindex('%'+@spliter+'%',@string))

insert into @table(items)

select @file

set @string=substring(@string,patindex('%'+@spliter+'%',@string)+1,len(@string))

end

if patindex('%'+@spliter+'%',@string)=0

begin

select @file=substring(@string,0,len(@string)+1)

insert into @table(items)

select @file

end

return

end

go

之后测试:select * from   dbo.uf_GetItemsInString('a&,bs,c' ,',')

就可以得出 a& bs c

//查找第n个分割符的位置

ALTER FUNCTION [dbo].[fn_SplitorIndex] (
    @array nvarchar(1000),
    @separator char=',',
    @idx int
)
RETURNS int
as
BEGIN
    if(@array is null or len(ltrim(rtrim(@array)))=0)
        return 0;

    DECLARE @item nvarchar(4000)
    DECLARE @cur  int,@index int
    DECLARE @pos int
    DECLARE @len  int    --字符串的长度
    set @index=1
    set @cur=1
    SET @len=len(@array)
    WHILE (@cur<=@len)
    BEGIN
        set @pos=CharIndex(@separator,@array,@cur)
        -- 如果有连续两个分隔符@separator，则跳过此次循环
        if (@index>@idx)
        break
        if(@pos=@cur)
        begin
            set @cur=@cur+1
            set @index=@index+1
            continue
        end
        if(@pos>0)
            begin
                set @item=SUBSTRING(@array,@cur,@pos-@cur)
                set @cur=@pos+1
                set @index=@index+1
            end
        else
            begin
                -- 循环已到了@array的末尾，在末尾并不包含分隔符@separator。
                set @item=SUBSTRING(@array,@cur,@len-@cur+1)
                set @cur=@len+1
            end
      
    END
    RETURN @cur-1   
end