I have a field number of type varchar. Even though it is of type varchar, it stores integer values with optional leading zeros. A sort orders them lexicographically ("42" comes before "9"). How can I order by numeric values ("9" to come before "42")?

Currently I use the query:

SELECT * FROM table ORDER BY number ASC
mysql sorting
Edit
Report