id tag
-----------------------------
1 飞机,大炮,轮船,火箭,火箭,飞机
2 ,,,,飞机,楼房,,,,,,
3 ,,,,,,,,,,
4 ,飞机,,火箭,,大炮
5 飞机,飞机,飞机,,
-----------------------------
大侠请看上图~ 很不规则!
我要的存储过程就是去除这些条数据的多余和重复的逗号和值!执行存储过程后结果应该是:
id tag
-----------------------------
1 飞机,大炮,轮船,火箭
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机
-----------------------------本人绝对的超级菜鸟~大侠门多多帮忙,!!!多多跟贴!
先深深的给你们鞠个躬了!!
-----------------------------
1 飞机,大炮,轮船,火箭,火箭,飞机
2 ,,,,飞机,楼房,,,,,,
3 ,,,,,,,,,,
4 ,飞机,,火箭,,大炮
5 飞机,飞机,飞机,,
-----------------------------
大侠请看上图~ 很不规则!
我要的存储过程就是去除这些条数据的多余和重复的逗号和值!执行存储过程后结果应该是:
id tag
-----------------------------
1 飞机,大炮,轮船,火箭
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机
-----------------------------本人绝对的超级菜鸟~大侠门多多帮忙,!!!多多跟贴!
先深深的给你们鞠个躬了!!
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,' select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
select * from tb1
--结果
id tag
----------- --------------------------------
1 飞机,大炮,轮船,火箭,火箭,飞机
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机,飞机,飞机(5 行受影响)
create procedure prochar
as
select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
select * from tb1
insert into t select 1,'飞机,大炮,轮船,火箭,火箭,飞机'
insert into t select 2,',,,,飞机,楼房,,,,,,'
insert into t select 3,',,,,,,,,,,'
insert into t select 4,',飞机,,火箭,,大炮'
insert into t select 5,'飞机,飞机,飞机,,'
gocreate function f_str(@tag varchar(100))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=','
set @tag=@tag+',' while(charindex(',',@tag))>0
begin
if (charindex(left(@tag,charindex(',',@tag)),@str)=0)
set @str=@str+left(@tag,charindex(',',@tag))
set @tag=stuff(@tag,1,charindex(',',@tag),'')
end
set @str=case when len(@str)>1 then substring(@str,2,len(@str)-2) else '' end
return @str
end
goselect id,dbo.f_str(tag) as tag from t
go/*
id tag
----------- -------------------------------
1 飞机,大炮,轮船,火箭
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机*/drop function f_str
drop table t
go
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,'
create procedure prochar
as
select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
--续上文
--分拆函数
CREATE FUNCTION dbo.f_split(
@id int , --tb中的id
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int,tag varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(@id,LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@id,@s)
RETURN
END
--建立拆分临时表,拆分字符串
create table #
(
id int ,
tag varchar(500)
)
go
--将数据拆分至临时表中
declare @i int ,@id int,@tag varchar(500),@cnt int
set @i=1
select @cnt=count(*) from tb1
while @i<=@cnt
begin
select @id=id,@tag=tag from tb1 where id = @i
insert into # select * from dbo.f_split(@id,@tag,',')
set @i=@i+1
end
---将拆分后结果合并更新至tb1中
declare @s varchar(500)
select @cnt=count(*) from tb1
set @i=1
while @i<=@cnt
begin
set @s=''
select @s=@s+','+tag from (select distinct id,tag from #) b where b.id=@i
update tb1 set tag =stuff(@s,1,1,'') where id=@i
set @i=@i+1
end
drop table #
select * from tb1--结果
/*
id tag
----------- ---------------------
1 大炮,飞机,火箭,轮船
2 飞机,楼房
3
4 大炮,飞机,火箭
5 飞机(5 行受影响)
*/
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,'
go
CREATE FUNCTION dbo.f_split(
@id int , --tb中的id
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int,tag varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(@id,LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@id,@s)
RETURN
END
go
--将数据拆分至临时表中
declare @i int ,@id int,@tag varchar(500),@cnt int
set @i=1
select @cnt=count(*) from tb1
while @i<=@cnt
begin
select @id=id,@tag=tag from tb1 where id = @i
insert into # select * from dbo.f_split(@id,@tag,',')
set @i=@i+1
end
---将拆分后结果合并更新至tb1中
declare @s varchar(500)
select @cnt=count(*) from tb1
set @i=1
while @i<=@cnt
begin
set @s=''
select @s=@s+','+tag from (select distinct id,tag from #) b where b.id=@i
update tb1 set tag =stuff(@s,1,1,'') where id=@i
set @i=@i+1
end
drop table #
select * from tb1
create procedure prochar
as
select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
go
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,'
go
CREATE FUNCTION dbo.f_split(
@id int , --tb中的id
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int,tag varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(@id,LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@id,@s)
RETURN
END
go
select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
go
--将数据拆分至临时表中
declare @i int ,@id int,@tag varchar(500),@cnt int
set @i=1
select @cnt=count(*) from tb1
while @i<=@cnt
begin
select @id=id,@tag=tag from tb1 where id = @i
insert into # select * from dbo.f_split(@id,@tag,',')
set @i=@i+1
end
---将拆分后结果合并更新至tb1中
declare @s varchar(500)
select @cnt=count(*) from tb1
set @i=1
while @i<=@cnt
begin
set @s=''
select @s=@s+','+tag from (select distinct id,tag from #) b where b.id=@i
update tb1 set tag =stuff(@s,1,1,'') where id=@i
set @i=@i+1
end
drop table #
select * from tb1
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,'
go
CREATE FUNCTION dbo.f_split(
@id int , --tb中的id
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int,tag varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(@id,LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@id,@s)
RETURN
END
goselect * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','
go
create table #
(
id int ,
tag varchar(500)
)
go --将数据拆分至临时表中
declare @i int ,@id int,@tag varchar(500),@cnt int
set @i=1
select @cnt=count(*) from tb1
while @i<=@cnt
begin
select @id=id,@tag=tag from tb1 where id = @i
insert into # select * from dbo.f_split(@id,@tag,',')
set @i=@i+1
end
---将拆分后结果合并更新至tb1中
declare @s varchar(500)
select @cnt=count(*) from tb1
set @i=1
while @i<=@cnt
begin
set @s=''
select @s=@s+','+tag from (select distinct id,tag from #) b where b.id=@i
update tb1 set tag =stuff(@s,1,1,'') where id=@i
set @i=@i+1
end
drop table #
select * from tb1
--把表和函数删一下,运行这个,上两个落下临时表了。这个可直接运行得出结果。
(
id int,
tag varchar(500)
)
insert into tb1
select 1,'飞机,大炮,轮船,火箭,火箭,飞机' union all
select 2,',,,,飞机,楼房,,,,,,' union all
select 3,',,,,,,,,,,' union all
select 4,',飞机,,火箭,,大炮' union all
select 5,'飞机,飞机,飞机,,'
go
CREATE FUNCTION dbo.f_split(
@id int , --tb中的id
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(id int,tag varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(@id,LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@id,@s)
RETURN
END
go
create procedure protest
as
select * from tb1 where charindex(',,',tag)>0
while @@rowcount>0
begin
update tb1 set tag=replace(tag,',,',',') where charindex(',,',tag)>0
end
update tb1 set tag=stuff(tag,1,1,'') where left(tag,1)=','
update tb1 set tag=left(tag,len(tag)-1) where right(tag,1)=','create table #
(
id int ,
tag varchar(500)
)
--将数据拆分至临时表中
declare @i int ,@id int,@tag varchar(500),@cnt int
set @i=1
select @cnt=count(*) from tb1
while @i<=@cnt
begin
select @id=id,@tag=tag from tb1 where id = @i
insert into # select * from dbo.f_split(@id,@tag,',')
set @i=@i+1
end
---将拆分后结果合并更新至tb1中
declare @s varchar(500)
select @cnt=count(*) from tb1
set @i=1
while @i<=@cnt
begin
set @s=''
select @s=@s+','+tag from (select distinct id,tag from #) b where b.id=@i
update tb1 set tag =stuff(@s,1,1,'') where id=@i
set @i=@i+1
end
drop table #
go
执行上面的语句。
EXEC protest--执行过程
select * from tb1 --最终结果,晕死了。
insert ta select 1, '飞机,大炮,轮船,火箭,火箭,飞机'
insert ta select 2, ',,,,飞机,楼房,,,,,,'
insert ta select 3, ',,,,,,,,,,'
insert ta select 4, ',飞机,,火箭,,大炮'
insert ta select 5, '飞机,飞机,飞机,,'
--创健存储过程:
create proc test_p
as
begin
SELECT TOP 100 id = IDENTITY(int, 1, 1)
INTO #a --创建临时辅助表
FROM syscolumns a, syscolumns bSELECT distinct A.id,
tag=SUBSTRING(A.tag, B.id, CHARINDEX(',', A.tag + ',', B.id) - B.id),con=identity(int,1,1)
into #b--生成新表
FROM ta A, #a B
WHERE SUBSTRING(',' + A.tag, B.id, 1) = ','update ta set tag=null--更新tag为空while exists(select 1 from #b)
begin
update a
set tag=isnull(a.tag+',','')+t.tag
from ta a,#b t
where a.id=t.id
and not exists (select 1 from #b where id=t.id and con<t.con)delete t
from #b t
where not exists (
select 1 from #b where id=t.id and con<t.con)
end
drop table #b,#a
update ta set tag=stuff(tag,1,1,'') where left(tag,1)=','
update ta set tag=stuff(tag,len(tag),1,'') where right(tag,1)=','
end
--测试:
exec test_p
--查询:
select * from ta--drop table ta
--drop proc test_p
id tag
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 大炮,飞机,火箭,轮船
2 飞机,楼房
3
4 大炮,飞机,火箭
5 飞机(5 行受影响)
用2005实现起来就要方便很多...
--delete a from #b a where exists(select 1 from #b where id=a.id and tag=a.tag and con<a.con)
--or ((select count(distinct tag) from #b where id=a.id)>1 and tag='')create table ta(id int,tag varchar(1000))
insert ta select 1, '飞机,大炮,轮船,火箭,火箭,飞机'
insert ta select 2, ',,,,飞机,楼房,,,,,,'
insert ta select 3, ',,,,,,,,,,'
insert ta select 4, ',飞机,,火箭,,大炮'
insert ta select 5, '飞机,飞机,飞机,,'
go--创健存储过程:
create proc test_p
@i int=1--变量默认为1,变量条件限制要更新的数据
as
begin
set nocount on--不显示操作影响记录
SELECT TOP 1000 id = IDENTITY(int, 1, 1)
INTO #a --创建临时辅助表
FROM syscolumns a, syscolumns bSELECT A.id,
tag=SUBSTRING(A.tag, B.id, CHARINDEX(',', A.tag + ',', B.id) - B.id),con=identity(int,1,1)
into #b--生成新表
FROM ta A, #a B
WHERE SUBSTRING(',' + A.tag, B.id, 1) = ',' and A.id>=@idelete a from #b a where exists(select 1 from #b where id=a.id and tag=a.tag and con<a.con)
or ((select count(distinct tag) from #b where id=a.id)>1 and tag='')--这两句加上*update ta set tag=null where ta.id>=@i--更新tag为空while exists(select 1 from #b)
begin
update a
set tag=isnull(a.tag+',','')+t.tag
from ta a,#b t
where a.id=t.id and A.id>=@i
and not exists (select 1 from #b where id=t.id and con<t.con)delete t
from #b t
where not exists (
select 1 from #b where id=t.id and con<t.con)
enddrop table #a,#b--删除临时表update ta set tag=stuff(tag,1,1,'') where left(tag,1)=','
update ta set tag=stuff(tag,len(tag),1,'') where right(tag,1)=','
set nocount off
endgo
--测试1:
exec test_p 5
--查询:
select * from ta--查看id为5的记录--测试2:
exec test_p
--查询:
select * from ta--drop table ta
--drop proc test_pid tag
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 飞机,大炮,轮船,火箭
2 飞机,楼房
3
4 飞机,火箭,大炮
5 飞机(所影响的行数为 5 行)
在这里排除:
delete a from #b a where exists(select 1 from #b where id=a.id and tag=a.tag and con<a.con)
or ((select count(distinct tag) from #b where id=a.id)>1 and tag='')--这两句加上*