createprocedure sp_split_string ( @stringNVARCHAR(4000) ) AS BEGIN DECLARE@object_idnvarchar(500) DECLARE@iINT DECLARE@lenINT print@string IF (@stringISNULL) OR (LTRIM(@string) ='') RETURN WHILECHARINDEX(',',@string) >0 BEGIN SET@len=LEN(@string) SET@i=CHARINDEX(',', @string) SET@object_id=LEFT(@string, @i-1) INSERTINTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可 SET@string=RIGHT(@string, @len-@i) END SET@object_id=@string INSERTINTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可 END go --测试 -- select * from a -- exec sp_split_string '102,103,105,106,107,108,200,500,306,408' -- select * from a
2.一个用于处理分隔","的函数tf_split_str
--1. 建立测试数据 --1.1 create table TypeAdv CREATETABLE TypeAdv ( id int, TypeName varchar(200) ) --1.2 insert data insertinto TypeAdv(id,TypeName) values(1,'电脑网络') insertinto TypeAdv(id,TypeName) values(2,'游戏动漫') insertinto TypeAdv(id,TypeName) values(3,'软件下载') insertinto TypeAdv(id,TypeName) values(4,'商务商铺') insertinto TypeAdv(id,TypeName) values(5,'建站服务') insertinto TypeAdv(id,TypeName) values(6,'门户综合') insertinto TypeAdv(id,TypeName) values(7,'影视音乐') insertinto TypeAdv(id,TypeName) values(8,'休闲娱乐') insertinto TypeAdv(id,TypeName) values(9,'生活资讯') insertinto TypeAdv(id,TypeName) values(10,'文学小说') --1.3 create table TypeAdv CREATETABLE GroupAdv ( id int, GroupName varchar(200), IntentionSet varchar(200) ) --1.4 insert data insertinto GroupAdv(id,GroupName,IntentionSet) values(5,'广告分组一','1,2,3,4,5,6,7,10') insertinto GroupAdv(id,GroupName,IntentionSet) values(6,'广告分组二','1,2,3,4,5,6') --2. 创建分隔“,”的函数 CREATEFUNCTION tf_split_str ( @stringNVARCHAR(4000) ) RETURNSvarchar(4000) AS BEGIN DECLARE@object_idnvarchar(400) DECLARE@iINT DECLARE@lenINT declare@returnvarchar(400) set@return='' IF (@stringISNULL) OR (LTRIM(@string) ='') RETURN@return WHILECHARINDEX(',',@string) >0 BEGIN SET@len=LEN(@string) SET@i=CHARINDEX(',', @string) SET@object_id=LEFT(@string, @i-1) select@return=@return+','+TypeName from TypeAdv where id =@object_id SET@string=RIGHT(@string, @len-@i) END SET@object_id=@string select@return=@return+','+TypeName from TypeAdv where id =@object_id set@return=stuff(@return,1,1,'') RETURN@return END --3.测试 select b.id,b.GroupName,dbo.tf_split_str(b.IntentionSet) from GroupAdv b