错了应该是 select trim(substr(字段,1,instr(字段,'1')-1))||trim(to_char((to_number(substr(字段,instr(字段,'1')))+1),'000')) from dual
即如果只有这几个数据 那么 update table_a set number = trim(substr(number ,1,instr(number ,'1')-1))||trim(to_char((to_number(substr(number ,instr(number ,'1')))+1),'000'))
用substr将最后三位分离出来tonumber 加一,再连山前面的部分SELECT substr('abc100',0,length('abc100')-3)||to_char(to_number(SUBSTR('abc100',length('abc100')-3+1,3))+1) as newvalue from dual;
select trim(substr(字段,1,instr(字段,'1')-1))||trim(to_char((to_number(substr(字段,instr(字段,'1')))+1),'000')) from dual
那么
update table_a set number = trim(substr(number ,1,instr(number ,'1')-1))||trim(to_char((to_number(substr(number ,instr(number ,'1')))+1),'000'))
as newvalue from dual;