SQL order string as number

Question

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 the character not as a number while sorting.

In the database I have

1 2 3 4 5 6 7 8 9 10...

On my page it shows ordered list like this:

1 10 2 3 4 5 6 7 8 9

How can I make it appear ordered by numbers ascending?

Answer 1

If possible you can change the data type of the column to a number if you are only storing the numbers. But, if you can't do that then cast your column value to an integer explicitly like this:

select col from yourtable

order by cast(col as unsigned)

Otherwise, implicitly for instance, with a mathematical operation which forces a conversion to the number, you can don this way:

select col from yourtable

order by col + 0

Note: MySQL converts strings from left to right. 

For Example:

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 */