Search code examples
mysqlvarchar

MySQL: Using sum on a varchar with comma?

I have a column, called net_amount, it contains values like 244,98. Its a varchar column. When I try to sum it using the sum function, it only sums the 244 and skipts the decimal places. I tried casting it to decimal like this:

select cast(net_amount as decimal) from mytable

This skips the decimal places as well ... any idea what might be wrong?

Thanks!

Solution

  • You could use REPLACE to replace comma to dot:

    SELECT REPLACE('244,98', ',', '.') * 1

    or you can use CAST like this:

    cast(REPLACE(net_amount, ',', '.') as decimal(8,2))