Thanks, but unfortunately the replace function does not want to work on a regexp in version 3.23...
I guess I'll have to create a "displacement" field and populate it from the admin tool. Thanks for your help. I will upgrade this server as soon as I can. -----Original Message----- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 8:14 AM To: Nicolas Verhaeghe; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? Nicolas, Not sure when the "replace" function was introduced into mysql, but I think it might do... Use "replace" in your order by, replacing a-z with null chars, leaving just your numeric digits, then order by select * from test; +-----+-----------+ | uid | name | +-----+-----------+ | 1 | george099 | | 2 | george100 | | 3 | george101 | | 4 | george001 | | 5 | 123bill | | 6 | 100bill | | 13 | george | | 14 | darren | | 15 | joe | | 16 | bill | +-----+-----------+ 10 rows in set (0.00 sec) mysql> select uid,name from test order by replace(name,'[a-z]',''); +-----+-----------+ | uid | name | +-----+-----------+ | 6 | 100bill | | 5 | 123bill | | 16 | bill | | 14 | darren | | 13 | george | | 4 | george001 | | 1 | george099 | | 2 | george100 | | 3 | george101 | | 15 | joe | +-----+-----------+ You might need to convert 'name' to uppercase to work with all your part numbers. select uid,name from test order by replace(upper(name),'[A-Z]',''); -- George -----Original Message----- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 9:13 AM To: 'Gleb Paharenko'; mysql@lists.mysql.com Subject: RE: How can I isolate the integer part of a varchar field and use it in an ORDER BY? 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] -- 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]
