By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select '100-1234-5' as str from dual union all
select '100-0004567-00' from dual union all
select '300-122334556-87' from dual
3 rows affected
select (regexp_substr(str, '[^-]+', 1, 1) ||
rpad('0', 19 - length(str), '0') ||
regexp_substr(str, '[^-]+', 1, 2) || '-' ||
regexp_substr(str, '[^-]+', 1, 3)
)
from t
| (REGEXP_SUBSTR(STR,'[^-]+',1,1)||RPAD('0',19-LENGTH(STR),'0')||REGEXP_SUBSTR(STR,'[^-]+',1,2)||'-'||REGEXP_SUBSTR(STR,'[^-]+',1,3)) |
|---|
| 1000000000001234-5 |
| 100000000004567-00 |
| 300000122334556-87 |
select length('30000122334556-87') from dual
| LENGTH('30000122334556-87') |
|---|
| 17 |