I have a table in MYSQL (you can see here link ), where the price is commas and not points:
2.33 11.00
When I will give an order by:
ORDER BY preco ASC
The system understands that it is a string and puts the 11.00 in front, how to do this conversion to the number at the time of ORDER BY?
Just to tell, I can not modify the table, it is not mine and I do not have this permission.
In your specific case you should make the transition to type decimal(18,2) , but there is an option that can cause a bit of slowness in the query, but it works in a general way:
SELECT * FROM TABELA
ORDER BY CAST(REPLACE(REPLACE(preco, '.', ''),',','.') AS DECIMAL(18,2))
If the price is commas, the field is probably not of the numeric type. Ideally you would change the data type of the price field.
But if you need this fast, try
select cast(REPLACE(preco, ',', '.') as float ) p from tabela order by p
or try to give update
update tabela set preco = REPLACE(preco, ',', '.');
Then change the data type from the price column to float.
As already answered, the only real solution is to correct the table field.
In any case, if it is to maintain the "compatibility mode", if the decimals are fixed , this is enough, to simplify the expression:
SELECT * FROM produto ORDER BY LPAD(preco,10,'0')
See working in your own SQL SQL Fiddle .
Change the maximum 10 houses in the field. Basically we are filling with zeros on the left. The 10 is the final number of houses, and 0 the alignment character.
This works because we start arranging the strings alphabetically like this:
0000002,00
0000004,10
0000011,50
I do not have the schema of your table here but try to do so:
order by cast (preco as real)asc
Try the following:
order by cast(replace(valor, '.', ',') as decimal(10,2));