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

Assuming your strings always end with 3 digits you could use RIGHT:

SELECT id, name
FROM Table1
ORDER BY RIGHT(name, 3);

Result:

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

MySQL doesn't have support for functional indexes, so this query will be relatively slow. It would be better to redesign your database (e.g. store the number separately) so that ordering on the value of a function isn't necessary.