--創建存儲過程 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 BEGINdeclare @totalLength intdeclare @length intdeclare @tag varchar(20)declare @currentNumber intset @tag=',' --分割符set @totalLength=len(@strID)--如果字符串不為空，則判斷是否是以逗號結尾，避免漏掉最后一個字符值if @totalLength>0beginif right(@strID,1)<>','select @strID=@strID+','endset @length=charindex(@tag,@strID)exec('insert into '+@tableName+'(code)values(left('''+@strID+''','+@length+'-1))')set @strID=substring(@strID,@length+1,@totalLength)set @currentNumber=1while (@length>0)beginset @currentNumber=@currentNumber+1set @length=charindex(@tag,@strID)if(@length=0)beginbreak;endexec('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

總結

以上是生活随笔為你收集整理的sql分割以逗号隔开的字符串的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。