11 messages in com.mysql.lists.mysqlHow can I isolate the integer part of...
FromSent OnAttachments
Nicolas Verhaeghe23 Jan 2006 21:09 
Gleb Paharenko24 Jan 2006 02:41 
Nicolas Verhaeghe24 Jan 2006 06:12 
George Law24 Jan 2006 07:14 
Nicolas Verhaeghe24 Jan 2006 08:08 
George Law24 Jan 2006 08:47 
Michael Stassen24 Jan 2006 21:11 
Nicolas Verhaeghe24 Jan 2006 21:13 
Michael Stassen24 Jan 2006 21:54 
Michael Stassen24 Jan 2006 21:59 
Nicolas Verhaeghe24 Jan 2006 22:05 
Subject:How can I isolate the integer part of a varchar field and use it in an ORDER BY?
From:Nicolas Verhaeghe (nico@whiteecho.com)
Date:01/23/2006 09:09:53 PM
List:com.mysql.lists.mysql

A client of mine sells motorcycle parts and the motorcycle models are for instance:

YZ85 YZ125 WRF450 YZF450

Etc...

If you know motorcycles, you know that the number is always the displacement in cc.

What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order.

Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85

I would like them sorted like this: YZ85 YZ125 WRF450 YZF450

The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC

(Yes, those are Yamahas and KTMs, for those who are into that type of vehicles).

How can I achieve this goal without creating a specific field in the database?

I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer "extracts" the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success.

Thanks a lot for your help!