How to sort varchar "numerically"?

Forum Moderators: open

Message Too Old, No Replies

How to sort varchar "numerically"?

How to sort interest rates when characters are involved?

suga

12:14 am on Apr 18, 2008 (gmt 0)

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

10:07 am on Apr 19, 2008 (gmt 0)

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

10:19 am on Apr 19, 2008 (gmt 0)

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

3:45 pm on Apr 19, 2008 (gmt 0)

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

5:37 pm on Apr 22, 2008 (gmt 0)

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