Because I am currently stuck with 3.23 I have just decided to create a "displacement" field to isolate the number. Besides, some of these bikes escape from the rules, for instance instead of 600 for 600cc, you only have a mere "6", "1" stands for 1000.
So all in all the displacement fields will work fine. -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 10:11 PM To: George Law Cc: 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? George Law wrote: > Nicolas, > > Not sure when the "replace" function was introduced into mysql, but I > think it might do... REPLACE() exists in 3.23. > Use "replace" in your order by, replacing a-z with null chars, leaving > just your numeric digits, then order by Easier said than done. > 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]',''); REPLACE doesn't accept patterns in the search string. This REPLACE is looking for a literal occurrence of the string '[a-z]' to be replaced with ''. > +-----+-----------+ > | uid | name | > +-----+-----------+ > | 6 | 100bill | > | 5 | 123bill | > | 16 | bill | > | 14 | darren | > | 13 | george | > | 4 | george001 | > | 1 | george099 | > | 2 | george100 | > | 3 | george101 | > | 15 | joe | > +-----+-----------+ Look again. Those are in alphabetical order, not numerical. > 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]',''); REPLACE is case-sensitive, but this method just won't work. mysql> SELECT REPLACE('123abcd45','[a-z]',''); +---------------------------------+ | REPLACE('123abcd45','[a-z]','') | +---------------------------------+ | 123abcd45 | +---------------------------------+ 1 row in set (0.11 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
