'Remove a decimal and concat zero at a specific position using PostgreSQL

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source