Hello. The "brute force" way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Nicolas Verhaeghe wrote: > 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! > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
