'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 |
---|