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

)

select

col_a,

regexp_replace(

regexp_replace(col_a,'(\d+)$','00000\1')

,'0*(\d{6})$'

,'\1'

) as col_b

from table_name

;