By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE 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;
4 rows affected
SELECT col_a,
SUBSTR(col_a, 1, 3) || LPAD(SUBSTR(col_a, 4), '6', '0') AS col_b
FROM table_name;
| COL_A | COL_B |
|---|---|
| ABC2001 | ABC002001 |
| ABC100145 | ABC100145 |
| ABC009282 | ABC009282 |
| ABC1901 | ABC001901 |