Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the moment.
So no SP... Is this possible at all with 3.23? Thanks for your help! -----Original Message----- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 3:41 AM To: mysql@lists.mysql.com Subject: Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY? 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
