[RESOLVED] Question about ORDER BY and VARCHAR

[nephish]

lo there, i have a table that stores a certain value in a VARCHAR(5) field, depending on the application, that could be a letter or a numeric value. My problem is with one query i am trying to pull off, i would only be selecting numbers, but when i do ORDER BY, it gives me the output sorted alphabetically.

like this:

"SELECT * FROM `Process` WHERE `first_spot` = '{$var}' ORDER BY `hi_value` "

would return the values of 101.11 as being lower than 93.2

i know that i should have made a seperate int field for these, when i first created it, but the table has millions of records now, and many scripts that depend on it to be this way,

is there a way i can order MySQL to order these value by numerical low to high anyway ?

thanks

[bogu]

The records are sorted as string not float, so mysql is doing a string sort, for this u should extract all the floats into an array, convert them to float and sort them using php ...

[Lars_Berg]

SELECT * 
 FROM `Process` 
WHERE `first_spot` = '{$var}' 
ORDER BY `hi_value` + 0

[nephish]

cool, thanks much !