How to sort mysql coloumn that has data in Bytes KB MB GB?

I have a table with column 'size' that has file sizes in diff units like '10 Kb', '50 MB', '1 GB'. The problem is when I try to sort, it returns 1 GB row at first.

How to solve this prob without changing old data ???

Any help is much appreciated... T开发者_运维百科hanks.

You should really either convert all data into a common unit (say bytes) or add a column which contains a "unit of measure" and keep the size column itself numeric.

Having said that, the following should work on data in that ends with either GB/MB/KB or B.

select size
  from t
 order 
    by case when size like '%GB' then power(1024, 3) * substr(size, 1, length(size) - 2)
            when size like '%MB' then power(1024, 2) * substr(size, 1, length(size) - 2)
            when size like '%KB' then power(1024, 1) * substr(size, 1, length(size) - 2)
            when size like '%B'  then                  substr(size, 1, length(size) - 1)
        end desc;


+-------+------------+
| size  | bytes      |
+-------+------------+
| 1GB   | 1073741824 |
| 10MB  | 10485760   |
| 100KB | 102400     |
| 1000B | 1000       |
+-------+------------+

Have size contain actual values and format them somewhere higher up (in your view).

Or you could make it ugly and write a function that takes a human readable value and returns the actual integer and then SORT BY foo(size). Did I mention it's ugly?

EDIT: Since you want to keep old data, here's another ugly solution:

SORT BY REPLACE(REPLACE(REPLACE(size, ' GB', '000 MB'), ' MB', '000 kB'), ' GB', '000')

(I'm on fire!)

Disclamer: You REALLY SHOULD change the data, it's the only way to keep it clean.