Home > Archive > PHP SQL > March 2005 > Query to list varchar in numerical order
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Andy Hassall 2005-03-28, 3:57 pm |
| On Mon, 28 Mar 2005 14:11:57 GMT, Scott <mail@REM0VETH1Strakhire.co.uk> wrote:
>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
|
|
|
|
|