select a,b,left(c,2)+ right('00000'+cast(cast(right(c,len(c)-2) as int)+1 as varchar),5)[c] from #表1

------解决方案--------------------
create table #表1(A varchar(10),B varchar(50),C varchar(50),D int )   
  
insert #表1 select    'aa ', 'aa00001 ', 'aa00100 ',100  

select left(rtrim(C),2)+ right(cast(cast(right(rtrim(C),5) as int )+100001 as char(6)),5)  from #表1

drop table #表1
=========================
aa00101

------解决方案--------------------
SQL code

create table #表1(A varchar(10),B varchar(50),C varchar(50),D int )     
  
insert #表1 select     'aa  ',  'aa00001  ',  'aa00100  ',100  

select left(rtrim(C),2)+ right(cast(cast(right(rtrim(C),5) as int )+100001 as char(6)),5)  from #表1 

drop table #表1 
========================= 
aa00101