You're very helpful, thanks. Problem already taken care of but thanks for the lesson.
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 10:55 PM To: Nicolas Verhaeghe Cc: 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 Verhaeghe wrote: > Thanks, but unfortunately the replace function does not want to work > on a regexp in version 3.23... or 4.0, or 4.1, or ... > I guess I'll have to create a "displacement" field and populate it > from the admin tool. Well, that's the right way to go. You're seeing the problem with the current scheme. Right now, your displacement is hidden inside the "model number", so it is difficult to look up the displacement. That is, the "model number" contains the answers to more than one question. That's usually a bad idea. It probably ought to be broken into separate columns. If you make a displacement column, you ought to be able to populate it using sql. See below. > Thanks for your help. I will upgrade this server as soon as I can. Upgrading is a good idea, but it won't help here. You can do this in sql, but it's a bit ugly. Here are the keys: * MySQL will pull out the number if it's at the beginning of the string. * You can change the beginning of the string with SUBSTRING(). * You can use CASE to handle the different possibilities. Putting them together gives you something like this: SELECT model FROM motorcycles ORDER BY CASE WHEN model > 0 THEN model + 0 WHEN SUBSTRING(model, 2) > 0 THEN SUBSTRING(model, 2) + 0 WHEN SUBSTRING(model, 3) > 0 THEN SUBSTRING(model, 3) + 0 WHEN SUBSTRING(model, 4) > 0 THEN SUBSTRING(model, 4) + 0 END; +--------+ | model | +--------+ | YZ85 | | YZ125 | | 125SX | | 250EXC | | WRF450 | | YZF450 | +--------+ 6 rows in set (0.00 sec) The first case handles the strings which start with a number. The second case handles the strings which starts with 1 letter before the number. The third case handles the string which start with 2 letters before the number. And so on. If you can have more than 3 letters before the number, you'll have to add the corresponding conditions. To just add and populate the displacement column, you could ALTER TABLE motorcycles ADD displacement INT, ADD INDEX disp_idx (displacement); UPDATE motorcycles SET displacement = CASE WHEN model > 0 THEN model + 0 WHEN SUBSTRING(model, 2) > 0 THEN SUBSTRING(model, 2) WHEN SUBSTRING(model, 3) > 0 THEN SUBSTRING(model, 3) WHEN SUBSTRING(model, 4) > 0 THEN SUBSTRING(model, 4) END; Then your query becomes simply SELECT model FROM motorcycles ORDER BY displacement; Better yet, the index on displacement can be used to speed up the ordering. Michael -- 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]
