How to sort varchar "numerically"?

How to sort interest rates when characters are involved?

[suga]

I have a database that stores interest rates. A small subset of data would be like this:

13.24
14.24
as low as 14.9
8.90
as low as 7.9

I need to be able to sort these interest rates from lowest to highest. so far, i have come up with this, but it's not sorting correctly:

select case
when upper(left(interest_rate,10)) = 'AS LOW AS ' then substr(interest_rate,11) else interest_rate end conv_interest_rate from rate_table order by conv_interest_rate asc;

thanks in advance.

[ashish21cool]

According to me its not proper to sort integer values and string values while sorting. Although you have defined both as varchar, ideally it shud be separate

[ashish21cool]

On the other hand, if you do sorting by using varchar datatype only then it will take the values as follows:
first digits (0 through 9) then UPPER CASE Alphabets (A,B...Z) and then LOWER CASE Alphabets (a.b....z) will be listed
or you might have to take substring and hell lot of other things to do.

[rocknbil]

You should be using the decimal data type. This **used** to be treated like varchar in searches, but is now treated as a numeric data type. I believe that will sort numerically (create a test table to find out. :-) ) The textual stuff can be appended to your output via the programming.

alter table mytable modify int_rates decimal (12,2) not null default '0.00';

[suga]

woohoo, figured it out. unfortunately, i'm still on 4.x, so i can't use convert.

select (case when upper(left(interest_rate,10)) = 'AS LOW AS' then substr(interest_rate,11) else interest_rate end)+0 conv_interest_rate from rate_table order by conv_interest_rate desc;

the "+0" converts varchar to numeric for sorting in mysql versions prior to 5.0