Remove a decimal and concat zero at a specific position using PostgreSQL-CodePudding

I have a column named invoice_number varchar(255) in the invoices table.

Here is some sample data:

20220010000000010
20220010000000011
20220010000000012

An invoice_number can have up to 17 digits. Here is the format in which it is generated:

Year(4 digits) Number of invoice (3 digits) profile number (10 digits)

At the moment, I have some data in this column as follows:

202200100000022.1
202200100000022.2
202200100000022.3

I would like to delete the decimal point which is the 2nd to the last digit and then add a zero on the 8th position (after 001 according to the sample data above) to handle all of these undesired invoice numbers.

Expected Output:

20220010000000221
20220010000000222
20220010000000223

What would be the best way to do this?

CodePudding user response：

A safe way to do it is using REGEXP_REPLACE.

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

invoice_number	new_invoice_number
202200100000022.1	20220010000000221
202200100000022.2	20220010000000222
202200100000022.3	20220010000000223

Test on db<>fiddle here