Query to list varchar in numerical order

I have a table which stores the size of photos in varchar(100) format.
When I query this using:-

$result=mysql_query("select distinct pid, p.*, i.* from products p, photos
i where p.hidden='N' and p.deleted='N' and p.photo=i.iid order by i.size,
p.name, p.make");

the results are ordered as:-

1 Kb
11 Kb
114 Kb
19 Kb
2 Kb
etc.....

When I would like them as:-

1 KB
2 Kb
11 Kb
19 Kb
114 Kb

I realise it would be better off changing my database format by stripping
out the 'Kb' and setting the data type to INT but for other reasons it
cannot be changed.

Is there a simple way that the query can be changed to strip out the 'Kb'
and convert 'i.size' to an INT value? (I have tried to use RTRIM but I
cannot seem to fathom the syntax)

I hope the above is clear.

Thanks for your time.

Scott

On Mon, 28 Mar 2005 14:11:57 GMT, Scott <mail@REM0VETH1Strakhire.co.uk> wrot
e:

>I have a table which stores the size of photos in varchar(100) format.
>When I query this using:-
>
>$result=mysql_query("select distinct pid, p.*, i.* from products p, photos
>i where p.hidden='N' and p.deleted='N' and p.photo=i.iid order by i.size,
>p.name, p.make");
>
>the results are ordered as:-
>
>1 Kb
>11 Kb
>114 Kb
>19 Kb
>2 Kb
>etc.....
>
>When I would like them as:-
>
>1 KB
>2 Kb
>11 Kb
>19 Kb
>114 Kb
>
>I realise it would be better off changing my database format by stripping
>out the 'Kb' and setting the data type to INT but for other reasons it
>cannot be changed.
>
>Is there a simple way that the query can be changed to strip out the 'Kb'
>and convert 'i.size' to an INT value? (I have tried to use RTRIM but I
>cannot seem to fathom the syntax)

Something vaguely like:

ORDER BY CAST(SUBSTRING(size, 1, INSTR(SIZE, ' ')) AS INTEGER)

? (i.e. order by everything up to the first space - the numeric part - and
treat it as a number).

(Blech! This is horrible)

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Scott wrote:
>
> I have a table which stores the size of photos
> in varchar(100) format.

Why???  This is a VERY bad design decision...

> the results are ordered as:-
>
> 1 Kb
> 11 Kb
> 114 Kb
> 19 Kb
> 2 Kb
> etc.....
>
> When I would like them as:-
>
> 1 KB
> 2 Kb
> 11 Kb
> 19 Kb
> 114 Kb
>
> I realise it would be better off changing my database format
> by stripping out the 'Kb' and setting the data type to INT but
> for other reasons it cannot be changed.

Then add a field...  And drop the old one in a future release.

> Is there a simple way that the query can be changed to strip
> out the 'Kb' and convert 'i.size' to an INT value?

An easier thing to do would be to leave `i.size` a string, but
cut out the 'kb' and left-pad it (say, to the length of 12)
with zeroes:

LPAD(REPLACE(LOWER(`i.size`), ' kb', ''), 12, '0')

Cheers,
NC