By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select invoice_number
, regexp_replace(invoice_number, '^([0-9]{4})([0-9]{3})([0-9]{8})[.]([0-9]+)$', '\1\20\3\4') as new_invoice_number
from (values
('202200100000022.1')
, ('202200100000022.2')
, ('202200100000022.3')
) q(invoice_number)
where invoice_number like '%.%';
| invoice_number | new_invoice_number |
|---|---|
| 202200100000022.1 | 20220010000000221 |
| 202200100000022.2 | 20220010000000222 |
| 202200100000022.3 | 20220010000000223 |