Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with table_name (col_a) AS (

SELECT 'ABC2001' FROM DUAL UNION ALL

SELECT 'ABC100145' FROM DUAL UNION ALL

SELECT 'ABC009282' FROM DUAL UNION ALL

SELECT 'ABC1901' FROM DUAL UNION ALL

-- other different prefixes:

select 'ABC2001' from dual union all

select 'AB100145' from dual union all

select 'A-BC9282' from dual union all

select 'A8C2374' from dual union all

select '7x-ABC32129' from dual union all

select '123ABC8942' from dual union all

-- long number:

select 'ABC123456789012345' from dual

)

select

v.*,

regexp_replace(

regexp_replace(col_a,'(\d+)$',rpad('0',max_num_length,'0')||'\1')

,'0*(\d{'||max_num_length||'})$'

,'\1'

) as col_b

from (

select t.*, max(length(regexp_substr(col_a,'\d+$')))over() as max_num_length

from table_name t

) v

;