Mysql and commas in decimal numbers

Some time ago i was working on a mess of a site: i had to fix some issues in order to make it work as long as needed to rewrite it from scratch. One of these issues involved a query that had to be ordered by a text field containing decimal numbers with commas instead of points.

In my first attempt i simply used CAST to convert text to decimal, something like:

ORDER BY CAST( price as decimal(10,2))

Unfortunately, mysql doesn’t like commas at all: it just “cuts” the number whenever it finds one. To get my queryset ordered properly i had to get rid of the commas, so i used replace:

ORDER BY CAST( replace(price,’,’,’.’) as decimal(10,2))

This tip is also useful when you have decimals with commas you want to display with points and vice versa.