sort by numeric part of a field

[andrea.demagistris]

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

[Shawn Hamzee]

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
11*********************@m73g2000cwd.googlegroups.c om,
"an****************@libero.it" <an****************@libero.itwrote:

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

[Bill Karwin]

an****************@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.

[andrea.demagistris]

yes!
very good, you solved my problem!
Thank you very much
Andrea

Bill Karwin ha scritto:

an****************@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.