SQL query problem

Question

I have one column of varchar type which store numbers like 10, 11, 16.5, 24, 43, 12, 100 etc I want to order these field. but it sort like these

10
11
12
16.5
100
24
43

expected

10
11
12
16.5
24
43
100

please help

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

order by 0+yourfield ASC