if exists(select 1 from table where fld1 like '1.1.2%')
begin
cast(cast(left(fld1,charindex('.',REVERSE(fld1))-1) as int)+1 as varchar)
update table set fld1= replace('~'+fld1,'~'+'1.1.2',
right(fld1,len(fld1)-charindex('.',REVERSE(fld1)))+cast (cast(left(fld1,charindex('.',REVERSE(fld1))-1) as int)+1 as varchar) )
where fld1 like '1.1.2%'
........
end
else
....
begin
cast(cast(left(fld1,charindex('.',REVERSE(fld1))-1) as int)+1 as varchar)
update table set fld1= replace('~'+fld1,'~'+'1.1.2',
right(fld1,len(fld1)-charindex('.',REVERSE(fld1)))+cast (cast(left(fld1,charindex('.',REVERSE(fld1))-1) as int)+1 as varchar) )
where fld1 like '1.1.2%'
........
end
else
....
你可以把'1.1.2'赋个一个变量,相应的地方改成变量名.if exists(select 1 from table where fld1 like '1.1.2%')
begin
update table set fld1= replace('~'+fld1,'~'+'1.1.2',
right(fld1,len(fld1)-charindex('.',REVERSE(fld1)))+cast (cast(left(fld1,charindex('.',REVERSE(fld1))-1) as int)+1 as varchar) )
where fld1 like '1.1.2%'
........
end
else
....
我还要update 1.1.3,1.1.4...呢。
概括一下就是1.1.*(大于等于2).* update 成1.1.*+1.*
create table table1(fld1 varchar(20))
insert into table1(fld1) values('1.1.1')
insert into table1(fld1) values('1.1.2')
insert into table1(fld1) values('1.1.3')
insert into table1(fld1) values('1.1.3.1')
insert into table1(fld1) values('1.1.3.2')
insert into table1(fld1) values('1.1.3.2.1')
insert into table1(fld1) values('1.1.4')
insert into table1(fld1) values('1.2')
insert into table1(fld1) values('2')
insert into table1(fld1) values('3')
insert into table1(fld1) values('4')go--创建存储过程
create procedure test
@aa varchar(20)
as
declare @aa1 varchar(20)
select @aa1=left(@aa,len(@aa)-1)+'['+right(@aa,1)+'-9]%' update table1 set fld1=left(fld1,len(@aa)-1)
+cast(cast(substring(fld1,len(@aa),1) as int)+1 as varchar)
+right(fld1,len(fld1)-len(@aa))
where fld1 like @aa1go--测试
select * from table1
exec test '1.1.2'
select * from table1go
drop procedure test
drop table table1
fld1
--------------------
1.1.1
1.1.2
1.1.3
1.1.3.1
1.1.3.2
1.1.3.2.1
1.1.4
1.2
2
3
4(所影响的行数为 11 行)
更换1.1.2后的结果fld1
--------------------
1.1.1
1.1.3
1.1.4
1.1.4.1
1.1.4.2
1.1.4.2.1
1.1.5
1.2
2
3
4
或者是1.1.201
这样你的脚本就不行了.:)
fld1 varchar(100))insert table1 select '1.1.1'
insert table1 select '1.1.2'
insert table1 select '1.1.3'
insert table1 select '1.1.3.1'
insert table1 select '1.1.3.2'
insert table1 select '1.1.3.2.1'
insert table1 select '1.1.4'
insert table1 select '1.2'
insert table1 select '2'
insert table1 select '3'
insert table1 select '4'create proc p11 (@s varchar(100))
as
declare @l varchar(100)
declare @r varchar(100)
declare @e varchar(100)
declare @s1 varchar(100)
declare @i int
set @i = 0
while charindex('.',@s,@i+1) > 0
begin
set @i = charindex('.',@s,@i+1)
print @i
end
set @r = right(@s, len(@s) - @i)
set @l = left(@s, @i)
while exists (select * from table1 where @s=fld1)
begin
set @r = rtrim(cast(cast(@r as int) + 1 as char))
set @s1 = @l + @r
set @e = replace(@s1, '.',':')
print @e
print @s
update table1 set fld1 = replace(fld1, @s, @e)
set @s = @s1
select * from table1 where @s=fld1
end
update table1 set fld1 = replace(fld1,':','.')
select * from table1
exec p11 '1.1.2'
insert #你的表 values('1.1.1')
insert #你的表 values('1.1.2')-- (1.1.3)
insert #你的表 values('1.1.3')-- (1.1.4)
insert #你的表 values('1.1.3.1')-- (1.1.4.1)
insert #你的表 values('1.1.3.2')-- (1.1.4.2)
insert #你的表 values('1.1.3.2.1')-- (1.1.4.2.1)
insert #你的表 values('1.1.4')-- (1.1.5)
insert #你的表 values('1.2')
insert #你的表 values('2')
insert #你的表 values('3')
insert #你的表 values('4')
declare @你传入的参数 varchar(10),@b varchar(10)
set @你传入的参数='1.1.2'
set @b=left(@你传入的参数,len(@你传入的参数)-charindex('.',REVERSE(@你传入的参数)))update #你的表 set fld1=@b+'.'+cast(substring(fld1,len(@b)+2,
case when charindex('.',fld1,len(@b)+2)-(len(@b)+2)>0 then charindex('.',fld1,len(@b)+2)-(len(@b)+2)
else 1 end)+1 as varchar(10))+substring(fld1,case when charindex('.',fld1,len(@b)+2)>0 then charindex('.',fld1,len(@b)+2) else 8000 end,8000)
where fld1>=@你传入的参数 and fld1 like @b+'%' and exists (select 1 from #你的表 where fld1=@你传入的参数)
select * from #你的表
go
drop table #你的表
drop table table1
create table table1 (
fld1 varchar(100))insert table1 select '1.1.1'
insert table1 select '1.1.2'
insert table1 select '1.1.3'
insert table1 select '1.1.3.1'
insert table1 select '1.1.3.2'
insert table1 select '1.1.3.2.1'
insert table1 select '1.1.4'
insert table1 select '1.2'
insert table1 select '2'
insert table1 select '3'
insert table1 select '4'drop proc p11
create proc p11 (@s varchar(100))
as
declare @l varchar(100)
declare @r varchar(100)
declare @e varchar(100)
declare @s1 varchar(100)
declare @i int
set @i = 0
while charindex('.',@s,@i+1) > 0
begin
set @i = charindex('.',@s,@i+1)
print @i
end
set @r = right(@s, len(@s) - @i)
set @l = left(@s, @i)
while exists (select * from table1 where @s=fld1)
begin
set @r = rtrim(cast(cast(@r as int) + 1 as char))
set @s1 = @l + @r
set @e = '*'+replace(@s1, '.',':')
print @e
print @s
update table1 set fld1 = replace(fld1, @s, @e) where @s = left(fld1,len(@s))
set @s = @s1
select * from table1 where @s=fld1
end
update table1 set fld1 = replace(fld1,':','.')
update table1 set fld1 = replace(fld1,'*','')
select * from table1
exec p11 '2'
declare @a1 varchar(60)
declare @a2 varchar(60)
declare @stra2 varchar(60)
declare @len integer
select @a1='1.1.'
select @a2='2'
select @len=len(@a1+@a2)
select @stra2=convert(varchar,convert(int,@a2)+1)
update table1
set fldl=@a1+@stra2+substring(fldl,@len1,len(dldl)-@len1)
where left(fldl,@len1)=@a1+@a2