拆分字符串，并存入临时表中

Posted on 2005-12-14 16:38 简单生活 阅读(133) 评论(0)  编辑  收藏 所属分类: 数据库

create table #mytable(account nvarchar(10))


declare @oldstring as nvarchar(3000)
declare @tempstring as nvarchar(10)
set @oldstring='10302299,10311441,10312196,10300104,10300297,10300381,10300509,10300564,10300679,10300732,10300743,10301331,10301338,10301359,10301414,10301785,10301787,10301910,10302121,10302160,10302220,10302269,10302983,10303821,10303913,10304481,10304659,10304671,10304680,10304691,10304698,10305624,10306061,10306815,10311833,10312241,10313045,10313120,'

while (PATINDEX('%,%',@oldstring)<>0)
begin
      set @tempstring=substring(@oldstring,1,PATINDEX('%,%',@oldstring)-1)
      insert into #mytable (account) values (@tempstring)--插入到临时表
      set @oldstring=substring(@oldstring,PATINDEX('%,%',@oldstring)+1,len(@oldstring))
end

select * from #mytable