declare @S VarChar(7) Set @s='991230' SELECT SUBSTRING('AAAAAAA',1,LEN((SubString(@S,1,LEN(@S)-LEN(CAST(CAST(@S As Int) As VarCHar(7)))))))+CAST(CAST(@S As Int) As VarChar(7)) SET @s='001023' SELECT SUBSTRING('AAAAAAA',1,LEN((SubString(@S,1,LEN(@S)-LEN(CAST(CAST(@S As Int) As VarCHar(7)))))))+CAST(CAST(@S As Int) As VarChar(7)) -----测试结果991230AA1023
我的比较繁琐,但功能实现了: --建表 create table student(sno char(20)) --插入数据 insert into student select '0032444' union all select '0d5555' union all select '1204441' --原始数据 sno -------------------- 0032444 0d5555 1204441 (3 row(s) affected) --更新 begin transaction ------------- declare @count int set @count=1 declare @sno_old char (20) declare @sno_new char (20) declare @i int set @i=1 declare cur_sno cursor for select sno from student open cur_sno fetch next from cur_sno into @sno_old while @@fetch_status=0 begin while substring(@sno_old,@count,1)='0' begin set @count=@count+1 end if @count>1 begin set @sno_new=right(rtrim(@sno_old),len(rtrim(@sno_old))-@count+1) end else begin set @sno_new=@sno_old end while @i<@count begin set @sno_new='a'+@sno_new set @i=@i+1 end update student set sno=@sno_new where sno=@sno_old set @count=1 set @i=1 fetch next from cur_sno into @sno_old end close cur_sno deallocate cur_sno --更新后的结果 sno -------------------- aa32444 ad5555 1204441 (3 row(s) affected)
Set @s='991230'
SELECT SUBSTRING('AAAAAAA',1,LEN((SubString(@S,1,LEN(@S)-LEN(CAST(CAST(@S As Int) As VarCHar(7)))))))+CAST(CAST(@S As Int) As VarChar(7))
SET @s='001023'
SELECT SUBSTRING('AAAAAAA',1,LEN((SubString(@S,1,LEN(@S)-LEN(CAST(CAST(@S As Int) As VarCHar(7)))))))+CAST(CAST(@S As Int) As VarChar(7))
-----测试结果991230AA1023
--建表
create table student(sno char(20))
--插入数据
insert into student
select '0032444'
union all select '0d5555'
union all select '1204441'
--原始数据
sno
--------------------
0032444
0d5555
1204441 (3 row(s) affected)
--更新
begin transaction
-------------
declare @count int
set @count=1
declare @sno_old char (20)
declare @sno_new char (20)
declare @i int
set @i=1
declare cur_sno cursor for
select sno from student
open cur_sno
fetch next from cur_sno into @sno_old
while @@fetch_status=0
begin
while substring(@sno_old,@count,1)='0'
begin
set @count=@count+1
end
if @count>1
begin
set @sno_new=right(rtrim(@sno_old),len(rtrim(@sno_old))-@count+1)
end
else
begin
set @sno_new=@sno_old
end
while @i<@count
begin
set @sno_new='a'+@sno_new
set @i=@i+1
end
update student
set sno=@sno_new
where sno=@sno_old
set @count=1
set @i=1
fetch next from cur_sno into @sno_old
end
close cur_sno
deallocate cur_sno
--更新后的结果
sno
--------------------
aa32444
ad5555
1204441 (3 row(s) affected)