| Michael Stassen 2006-01-25, 3:26 am |
| 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/mysql? unsub...sie.nctu.edu.tw
|