Ordering numbers in a text field 1

[jimoblak]

I have a series of values in a varchar field. I need to display them in an ordered list as:

32
51
75
80
100

Unfortunately, since it is a text field, MySQL sorts the values as:

100
32
51
75
80

Is there any way to get these to sort numerically?

I cannot add leading zeroes to the records. I cannot change the field type to an integer because other records include letters. I am using this with PHP so if it is not possible to sort in the SQL, maybe a PHP trick might do.

- - picklefish - -

 

[sleipnir214]

If you are running MySQL 4.0.2 or newer, you can typecast them:

ORDER BY CAST(columname AS SIGNED)

or

ORDER BY CAST(columname AS UNSIGNED)


If you are running MySQL 3.x, you might try forcing an intrinsic cast:

ORDER BY (columnname + 0)


Take a look here for more information:

http://www.mysql.com/doc/en/Cast_Functions.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[supermaestro]

What would happen if the data was stored in a text field like this:

32,000
51,000
75,000
80,000
100,000

How would you order these numerically?