I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances.
It is taking them as character not as number while sorting.
In the database, I have values like these:
1 2 3 4 5 6 7 8 9 10...
But when I sort them by this field I get a result like this:
1 10 2 3 4 5 6 7 8 9
How can I make it get results ordered numerically, ascending?
If possible you should change the data type of the column to a number if you only store numbers anyway.
If you can't do that then cast your column value to an integer explicitly with
select col from yourtable
order by cast(col as unsigned)
or implicitly for instance with a mathematical operation which forces a conversion to number
select col from yourtable
order by col + 0
BTW MySQL converts strings from left to right. Examples:
string value | integer value after conversion
--------------+--------------------------------
'1' | 1
'ABC' | 0 /* the string does not contain a number, so the result is 0 */
'123miles' | 123
'$123' | 0 /* the left side of the string does not start with a number */