Output list by numeric-alphabetic order - MYSQL

Question

I have a list of codes:

1A
1B
1C
2A
2B
2C
.....
10A
10B..
11A
11B..
...

But when I sort I get the below order (I have removed some values from the list to make it shorter)

10A
10A
10B
10B
11A
11A
1A
1A
1B
1B
1C
2B
2B
2B
2B
2C

How do I order the list by the numeric value and the alphabet in the proper order (asc)?

possible duplicate of [MySQL 'Order By' - sorting alphanumeric correctly](http://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) — , Nov 14 '12 at 18:47

score 2 · Answer 1 · answered Nov 14 '12 at 19:18

2

I didn't know that it was possible do do something like this, but it looks like it is:

SELECT code
FROM YourTable
ORDER BY Convert(code, unsigned), code

answered Nov 14 '12 at 19:18

fthiella

48,073
15
90
106

score 0 · Answer 2 · answered Nov 14 '12 at 18:45

mysql doesn't directly support "natural" sorting like that, but you can fake it up with a more complicated order by clause. However, since mysql doesn't have capturing regexes that'd let you find where the numeric/alpha split in those strings is, it'd be hideously ugly to write one that can appropriately split things up so you could do

SELECT ugly_split_function1(yourfield) AS alpha, ugly_splitfunction2(yourfield) AS numeric
....
ORDER BY numeric, alpha

The best solution would be to split up that field into two separate ones, so you can directly sort on the individual fields.

Make sure you convert `numeric` to an integer type, or it will still sort funky. You can also just do `SELECT yourfield` and move the splits into the `SORT` clause so you don't have to look at the ugliness in the result. — Sammitch, Nov 14 '12 at 18:59

score 0 · Answer 3 · answered Nov 14 '12 at 18:58

0

IF your codes are ALWAYS a number followed by ONLY 1 letter, the following should work...

 SELECT code FROM table ORDER BY LENGTH(code), code

answered Nov 14 '12 at 18:58

Tim Dearborn

1,178
7
18

Please note that the above will break if you have a changing number of letters that come after the number in your codes. – Tim Dearborn Nov 14 '12 at 18:59

Output list by numeric-alphabetic order - MYSQL

3 Answers3