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]
