Proper formatting of values in Postgres

Question

I have values in my column in postgres and I need help to transform in properly as part of my etl

Sample data

    Amount
$1,000.00
$3.000,00
$200.000,00
$1,234,567.40

as you can see the first and last row are properly formatted. This is what I have done so far

 SELECT
        amount,
        CAST(LEFT(amount, strpos(amount, '.')-1) || ',' || SUBSTRING(amount,(strpos(amount, '.')+1),3) || '.' || SUBSTRING(amount,(strpos(amount, ',')-1),2) AS varchar) AS Formattedstring1
        
    FROM AmountTAable

I am not getting the correct result like

  Amount
$1,000.00
$3,000.00
$200,000.00
$1,234,567.40

Please help with the correct format, thank you

Stu · Accepted Answer · 2022-01-05 23:30:27Z

1

can you try the following:

select cast(Replace(Replace(Replace(Amount,',',''),'.',''),'$','')::decimal(18,2)/100 as money) as Amount
from t

Example fiddle

answered Jan 5, 2022 at 23:30

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Proper formatting of values in Postgres

Sample data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample data

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related