Use multiple replace conditions for a single column in Amazon Redshift

Question

I have a table where the amount column has , and $ sign for example: $8,122.14 as values. I want to write a replace function to replace $ and , over that column in one go. Is there any way we can write multiple conditions in one replace in Redshift? Also, this is apart of post processing the data where I am inserting data from stage table to a final table after replacing these values.

I tried the ways listed in the take 1 and 2 given in the code but both of them failed.

Take 1: insert into db.stage_table select (coalesce(replace(logging_amount,'$',','),''))) as logging_amount from db.table;

Take 2: insert into db.stage_table select (coalesce(replace(logging_amount,'$',',')) as logging_amount from db.table;

Both of them failed.

The expected result should be replace function in a single statement.

Answer 1

Yes you can nest replace statements like this

replace(replace(logging_amount,'$',''),',','')

Or you can use regex if you prefer (personally for something like this i think nested replaces are easier to read.)