SQL query for sorting numeric fields

I have one column of the type VARCHAR which stores numbers like 10, 11, 16.5, 24, 43, 12, 100, etc.

I want to order these fields but it sorts like t开发者_Python百科his:

10
11
12
16.5
100
24
43

And I'd like this result:

10
11
12
16.5
24
43
100

How can I do this?

VARCHAR is a string type, so it's ordering them alphabetically, which is the expected behavior. If possible you should change the column to a number type. If that's not possible you can cast the value to a number like so:

SELECT ... ORDER BY CAST(column_name AS DECIMAL)

However, this will impact your performance if you have a lot of rows in your database.

order by to_number(mycol)

this will of course give an error if one value is not numeric. which begs the question - why not make it a numeric column in the first place.

You want to cast the field to be a numeric value instead, so that MySQL will order it with a numeric comparison, rather than a string comparison.

SELECT your_column 
FROM your_table 
ORDER BY CAST(your_column AS DECIMAL) ASC

The above will work with negative numbers too, if you have any of those in your table.

Although if the field only contains numeric data, you really should be storing it as such, rather than as a varchar. Having a cast in your query will dramatically affect performance as you get more rows.

Just add zero to your field without any cast or convert:

order by 0+yourfield ASC