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 v.*, prefix||num as col_b
from (
select
col_a,
rtrim(col_a,'0123456789') as prefix,
lpad(substr(col_a,1+length(rtrim(col_a,'0123456789'))),6,'0') as num
from table_name
) v
;
| COL_A | PREFIX | NUM | COL_B |
|---|---|---|---|
| ABC2001 | ABC | 002001 | ABC002001 |
| ABC100145 | ABC | 100145 | ABC100145 |
| ABC009282 | ABC | 009282 | ABC009282 |
| ABC1901 | ABC | 001901 | ABC001901 |
| ABC2001 | ABC | 002001 | ABC002001 |
| AB100145 | AB | 100145 | AB100145 |
| A-BC9282 | A-BC | 009282 | A-BC009282 |
| A8C2374 | A8C | 002374 | A8C002374 |
| 7x-ABC32129 | 7x-ABC | 032129 | 7x-ABC032129 |
| 123ABC8942 | 123ABC | 008942 | 123ABC008942 |