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]
