update tabname set AA =
right('0' + substring(AA, 1, charindex(AA,'-',0)),2)
+ '-' +
right('0' + substring(AA, charindex(AA,'-',0)+1, charindex(AA,'-',3)-charindex(AA,'-',0)),2)
+ '-' +
right('00' + right(len(AA)-charindex(AA,'-',3)),3)
right('0' + substring(AA, 1, charindex(AA,'-',0)),2)
+ '-' +
right('0' + substring(AA, charindex(AA,'-',0)+1, charindex(AA,'-',3)-charindex(AA,'-',0)),2)
+ '-' +
right('00' + right(len(AA)-charindex(AA,'-',3)),3)
right(cast((100+第一段int) as varchar(10)),2)+'-'+
right(cast((100+第二段int) as varchar(10)),2)+'-'+
right(cast((1000+第三段int) as varchar(10)),3)
create function f_test (@dt varchar(20))
returns varchar(20)
as
begin
declare @i int,
@str varchar(20)
set @i=1
set @dt = rtrim(@dt)
set @str=''
while charindex('-',@dt) <>0
begin
set @str = @str+replicate('0',2-charindex('-',@dt)+1)+left(@dt,charindex('-',@dt)-1)+'-'
set @dt= right(@dt,len(@dt)-charindex('-',@dt))
end
set @str= @str+replicate('0',3-len(@dt))+@dt
return (@str)
endselect f_test(字段) from 表
Select right('00'+b,2)+'-'+right('00'+c,2)+'-'+right('000'+right(a,len(a)-len(b)+len(c)-4),3) [D] FROM (select a,b,right(c,len(c)-len(b)-1) [c] FROM (select a,b,left(a,charindex('-',a,len(b)+2)-1) [c] FROM (select a,left(a,charindex('-',a)-1) [b] from t4)E ) F ) GGO原始表数据部分
create table T4( a nvarchar(10))
GO
insert into t4(a) values('1-1-1')
insert into t4(a) values('1-1-2')
insert into t4(a) values('1-1-3')
insert into t4(a) values('1-1-102')
insert into t4(a) values('2-1-1')
insert into t4(a) values('2-1-2')
insert into t4(a) values('2-1-526')
insert into t4(a) values('50-1-125')
GO