Click here to get back home

sort by numeric part of a field
 Home | NewsGroups | Search | About
Login Password
Register Now!
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
sort by numeric part of a field andrea.demagistris@libero.it 07-26-2006
Get Chitika Premium
Posted by andrea.demagistris@libero.it on July 26, 2006, 6:35 am
Please log in for more thread options
 Hello,
I must sort a table width only one column. Inside the field I have, for
example, hard disk capacity (something like '100Gb' or '80Gb' or
'80Gbyte' or 'Gb80').

So, if I use ORDER BY ... DESC, 80Gb will be before 100Gb and other
errors.

How can i sort the table only on the numeric part of the field?

I'm trying using CAST() but with no results.

Thank you very much
Andrea


Posted by Shawn Hamzee on July 26, 2006, 10:15 am
Please log in for more thread options
 I'd say to try to substring for G (both lower and upper case and any other
letters you may need for example M) and then drop everything after that
position. This will leave you only the digits.

-s


On 7/26/06 06:35, in article
1153910126.921460.51390@m73g2000cwd.googlegroups.com,

> Hello,
> I must sort a table width only one column. Inside the field I have, for
> example, hard disk capacity (something like '100Gb' or '80Gb' or
> '80Gbyte' or 'Gb80').
>
> So, if I use ORDER BY ... DESC, 80Gb will be before 100Gb and other
> errors.
>
> How can i sort the table only on the numeric part of the field?
>
> I'm trying using CAST() but with no results.
>
> Thank you very much
> Andrea
>


Posted by Bill Karwin on July 29, 2006, 9:00 pm
Please log in for more thread options
andrea.demagistris@libero.it wrote:
> How can i sort the table only on the numeric part of the field?

...ORDER BY 0 + harddiskcapacity

This coerces the column to be evaluated as an integer. It uses the
digits in the leading portion of the value, to form an integer.
In other words, the expression 0 + '80Gb' returns the integer value 80.

Regards,
Bill K.

Posted by andrea.demagistris@libero.it on August 1, 2006, 9:51 am
Please log in for more thread options
yes!
very good, you solved my problem!
Thank you very much
Andrea

Bill Karwin ha scritto:

> andrea.demagistris@libero.it wrote:
> > How can i sort the table only on the numeric part of the field?
>
> ...ORDER BY 0 + harddiskcapacity
>
> This coerces the column to be evaluated as an integer. It uses the
> digits in the leading portion of the value, to form an integer.
> In other words, the expression 0 + '80Gb' returns the integer value 80.
>
> Regards,
> Bill K.


Similar ThreadsPosted
update [table] select - but I only need part of the column field value to select January 26, 2006, 2:38 pm
Restoring Numeric Databases September 1, 2005, 7:46 pm
Sort, Limit then Sort August 11, 2005, 3:55 pm
MySQL uses only part of the key when I add an OR June 4, 2006, 3:34 am
Replicating part of database August 10, 2006, 12:43 pm
SQL HELP PLEASE!! Cursor only returns part of the data July 25, 2007, 8:30 pm
Extracting Field data and putting each field into a shell variable? June 19, 2006, 3:28 pm
Where clause - knowing what part results matched on - possible? April 16, 2006, 6:09 am
SETting a datetime field based on a text field September 29, 2006, 5:42 pm
I want field data without field name, how? July 19, 2006, 10:50 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap