Search code examples
sqlpostgresqlcastingsum

postgres cast "number" varchar with commas and dots into integer

I have a column that has a numeric value formatted as a string with commas and dots, e.g 1.000,50 or 100,00.

I want to cast these varchars into a number, in order to sum them in a query. How can I do this?

SELECT sum(replace(string_num, '.','')::REAL) FROM sales; --not working, just illustrating

I expect a sum of all the casted integers

Solution

  • I suspect that you want to get rid of the .s and replace the ,s with . and add as numerics:

    sum(replace(replace(string_num, '.', ''), ',', '.'))