safa balkı safa balkı - 1 month ago 15
MySQL Question

Sorting by Price SQL data type VARCHAR

I want to order by price but type is varchar

my query = Select cost FROM table order by cost asc

result :

1.100.000
3.400.000
5.500.000
142.000
199.000
238.000
287.500
315.000
380.000
900.000


should:

142.000
199.000
238.000
287.500
315.000
380.000
900.000
1.100.000
3.400.000
5.500.000


How can I do it??

Answer

You can convert to a number:

order by replace(price, '.', '') + 0

Assuming that no prices start with "0" and all have the same format, you can use:

order by length(price), price