TYPE CONVERSION: How do you convert a string to a decimal?

Question

I'm trying to sum a column of varchar data, however the values all have commas and decimal points in them. It appears that the query cannot convert a string to a decimal. Help!

I have tried the below query and keep getting the below error message:

SELECT SUM(Cast("Repaid to IDA (US$)" as DECIMAL(10,4)))
FROM "banking_table"
WHERE "Country" = 'Bolivia';

Error message:

Conversion Error: Could not convert string "12,063,443.31" to DECIMAL(10,4)

Answer 1

I had success with the following statement:

SELECT SUM(CAST(REPLACE(banking_data."Repaid to IDA (US$)", ',', '') AS DOUBLE)) FROM banking_data;

Not sure about this DECIMAL(10, 4) data type. According to the documentation it is unnecessary. And it throws an error when the strings in your table don't match the WIDTH and SCALE properties of it.

Edit: There was a problem with the parathenses in the first example.

As CSVFiddle doesn't allow sharing the whole fiddle including the data, a screenshot has to suffice: