sql分割以逗号隔开的字符串

--创建存储过程
IF EXISTS (SELECT * FROM sysobjects WHERE name='GetSplitString' AND xtype='p')
    DROP PROCEDURE dbo.GetSplitString
GO
CREATE PROCEDURE dbo.GetSplitString
    @strID nvarchar(max)    --以逗号隔开的字符串
    ,@tableName nvarchar(50) output    --临时表
AS
BEGIN

    declare @totalLength int
    declare @length int
    declare @tag varchar(20)
    declare @currentNumber int
    set @tag=','    --分割符
    set @totalLength=len(@strID)
    
    --如果字符串不为空，则判断是否是以逗号结尾，避免漏掉最后一个字符值
    if @totalLength>0
    begin
        if right(@strID,1)<>','
            select @strID=@strID+','
    end
    
    set @length=charindex(@tag,@strID)
    exec('insert into '+@tableName+'(code)values(left('''+@strID+''','+@length+'-1))')
    set @strID=substring(@strID,@length+1,@totalLength)
    set @currentNumber=1
    while (@length>0)
    begin
        set @currentNumber=@currentNumber+1
        set @length=charindex(@tag,@strID)
        if(@length=0)
        begin
            break;
        end
        exec('insert into '+@tableName+'(code)values(left('''+@strID+''','+@length+'-1))')
        set @strID=substring(@strID,@length+1,@totalLength)
    end
END
GO

--调用存错过程
IF EXISTS (SELECT * FROM sysobjects WHERE name='tbTemp' AND xtype='U')
    DROP TABLE tbTemp
GO
create table tbTemp(code nvarchar(20))
go
exec GetSplitString 'baidu,google,yahoo,my,db,sqlserver,helloworld','tbTemp'
go
select * from tbTemp
go
IF EXISTS (SELECT * FROM sysobjects WHERE name='tbTemp' AND xtype='U')
    DROP TABLE tbTemp
GO       

View Code

 

 

 

转载于:https://www.cnblogs.com/bingle/p/3258509.html