Alphanumeric Order By in Mysql

Question

How can i make only numeric order by when the column containing alphanumeric characters in mysql ?

column (name) is unique field.

my table contains the records,

id  name
1   ab001
2   ab010
3   aa002
4   ac004
5   ba015
6   ba006
7   aa005
8   ac003

The results must be like this,

id  name
1   ab001
3   aa002
8   ac003
4   ac004
7   aa005
6   ba006
2   ab010
5   ba015

When I am trying this query Select * from test order by name, I am getting the results order by alpha characters only. How do I get this ?

Answer 1

I would do this way:

select id, name from Table1 order by names + 0, names;

without ordering:

mysql> select * from alpha;
+---+-------+
| i | name  |
+---+-------+
| 1 | ab001 |
| 2 | ab010 |
| 3 | aa002 |
| 4 | cc001 |
| 5 | cb010 |
| 6 | aaa02 |
+---+-------+
6 rows in set (0.00 sec)

With my query:

mysql> select i, name from alpha order by name + 0, name;
+---+-------+
| i | name  |
+---+-------+
| 3 | aa002 |
| 6 | aaa02 |
| 1 | ab001 |
| 2 | ab010 |
| 5 | cb010 |
| 4 | cc001 |
+---+-------+
6 rows in set (0.00 sec)