1.存储过程实现
create procedure sp_split_string( @string NVARCHAR(4000))ASBEGIN DECLARE @object_id nvarchar(500) DECLARE @i INT DECLARE @len INT print @string IF (@string IS NULL) OR (LTRIM(@string) = '') RETURN WHILE CHARINDEX(',',@string) > 0 BEGIN SET @len = LEN(@string) SET @i = CHARINDEX(',', @string) SET @object_id = LEFT(@string, @i-1) INSERT INTO a (id) VALUES (@object_id)--少做修改，改成需要的sql语句即可 SET @string = RIGHT(@string, @len - @i) END SET @object_id = @string INSERT INTO a (id) VALUES (@object_id)--少做修改，改成需要的sql语句即可ENDgo--测试-- select * from a-- exec sp_split_string '102,103,105,106,107,108,200,500,306,408'-- select * from a2.一个用于处理分隔","的函数tf_split_str
--1. 建立测试数据--1.1 create table TypeAdvCREATE TABLE TypeAdv ( id int, TypeName varchar(200))--1.2 insert datainsert into TypeAdv(id,TypeName) values(1,'电脑网络')insert into TypeAdv(id,TypeName) values(2,'游戏动漫')insert into TypeAdv(id,TypeName) values(3,'软件下载')insert into TypeAdv(id,TypeName) values(4,'商务商铺')insert into TypeAdv(id,TypeName) values(5,'建站服务')insert into TypeAdv(id,TypeName) values(6,'门户综合')insert into TypeAdv(id,TypeName) values(7,'影视音乐')insert into TypeAdv(id,TypeName) values(8,'休闲娱乐')insert into TypeAdv(id,TypeName) values(9,'生活资讯')insert into TypeAdv(id,TypeName) values(10,'文学小说') --1.3 create table TypeAdvCREATE TABLE GroupAdv ( id int, GroupName varchar(200), IntentionSet varchar(200))--1.4 insert datainsert into GroupAdv(id,GroupName,IntentionSet) values(5,'广告分组一','1,2,3,4,5,6,7,10')insert into GroupAdv(id,GroupName,IntentionSet) values(6,'广告分组二','1,2,3,4,5,6')--2. 创建分隔“，”的函数CREATE FUNCTION tf_split_str( @string NVARCHAR(4000))RETURNS varchar(4000)ASBEGIN DECLARE @object_id nvarchar(400) DECLARE @i INT DECLARE @len INT declare @return varchar(400) set @return='' IF (@string IS NULL) OR (LTRIM(@string) = '') RETURN @return WHILE CHARINDEX(',',@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 @returnEND--3.测试select b.id,b.GroupName,dbo.tf_split_str(b.IntentionSet) from GroupAdv b