Proper formatting of values in Postgres-CodePudding

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

CodePudding user response：

can you try the following:

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

Example fiddle