Alphanumeric Order By in Mysql

Question

Hi,

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'

Answer 2

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)

Answer 3

I had a slightly different issue, but the second example worked for me. I'd vote it up, but I just got started here.

I was dealing with a single varchar column which could contain any kind of information, but it needed to sort properly no matter what was in it:

So my column could contain info such as:

AB CD EF GH

or

01 02 03 04

The answer from Xavier caused it to be in alphabetical order when the information was all alphabetical, or numeric when the info was numeric.

Thanks

Thanks!