以前加的数据.现在想把它删掉(更新).如
字段1 | 字段2 | 字段3 |
----------------------------------------------------
001 | aaa | 10,20,30,25,10 |
002 | ddd | 50,20,10 |
003 | dds | 15,10,42,10,47,58,10,80 |
......
上面字段3中有每行都一些数字,现在我想在字段3中有出现2次以上10的记录只保留一个10.不知道怎么删才是比较好有有效率???????
删掉后应该是:
字段1 | 字段2 | 字段3 |
----------------------------------------------
001 | aaa | 10,20,30,25 |
002 | ddd | 50,20,10 |
003 | dds | 15,10,42,47,58,80 |
字段1 | 字段2 | 字段3 |
----------------------------------------------------
001 | aaa | 10,20,30,25,10 |
002 | ddd | 50,20,10 |
003 | dds | 15,10,42,10,47,58,10,80 |
......
上面字段3中有每行都一些数字,现在我想在字段3中有出现2次以上10的记录只保留一个10.不知道怎么删才是比较好有有效率???????
删掉后应该是:
字段1 | 字段2 | 字段3 |
----------------------------------------------
001 | aaa | 10,20,30,25 |
002 | ddd | 50,20,10 |
003 | dds | 15,10,42,47,58,80 |
insert into ttt select '001','aaa','10,20,30,25,10'
union all select '002','ddd','50,20,10'
union all select '003','dds','15,10,42,10,47,58,10,80 '
update ttt set c=substring(c,1,charindex(',10,',','+c+',')+2)+replace(substring(c,charindex(',10,',','+c+',')+3,1000),',10','')
create function f_Del_repeat
(@v varchar(1000))
returns varchar(1000)
as
begin
declare @v1 varchar(1000),@v2 varchar(1000)
set @v1=@v
set @v2=''
while charindex(',',@v1)>0
begin
set @v2=@v2+left(@v1,charindex(',',@v1))
set @v1=replace(','+@v1+',',','+left(@v1,charindex(',',@v1)),',')
set @v1=case right(@v1,1) when ',' then left(@v1,len(@v1)-1) else @v1 end
set @v1=case left(@v1,1) when ',' then right(@v1,len(@v1)-1) else @v1 end
end
return @v2+@v1
end--然后执行
update table set col3=dbo.f_Del_repeat(col3)