How to sort alphanumeric data in mysql?

Question

Firstly I want to point out that I have tried almost everything. I am trying since last 8 hours to make my list in order, and I have applied dozen of solutions found here.

Here is SQL Fiddle with the sample data. I have found a page that manages to sort my list in the right order, and that is:

1

2

2.B3

5

9

10 A-1

10 A-3

10 B-4

10 B-5

11

12

B3-43

B3-44

B3 - 48

B3 - 49

Basztowa 3

Basztowa 4

Basztowa 5

Basztowa 7

Basztowa 9

D.1

D.2

D.10

D.11

D.12

Kabaty ul. Pod lipą 4

But I am not able to reproduce this using MySQL.

I would appreciate any help as I have no more ideas. I consider using PHP to sort my list but as far as I know DBMS are optimized for this kid of operations so if it's possible I would like to avoid doing this using PHP.

Answer 1

Technically, the mysql sorting mechanism works correctly but your strings are formatted in the wrong way. The underlying structure of your data is something like the following (Original column kept for ease of association to the example):

alpha1   num1 alpha2 num2 ...   Original      
            1                   1             
            2                   2             
            2      B    3       2.B3          
            5                   5             
            9                   9             
           10      A    1       10 A-1        
           10      A    3       10 A-3        
           10      B    4       10 B-4        
           10      B    5       10 B-5        
           11                   11            
           12                   12            
B           3          43       B3-43         
B           3          44       B3-44         
B           3          48       B3 - 48       
B           3          49       B3 - 49       
Basztowa    3                   Basztowa 3    
Basztowa    4                   Basztowa 4    
Basztowa    5                   Basztowa 5    
Basztowa    7                   Basztowa 7    
Basztowa    9                   Basztowa 9    
D           1                   D.1           
D           2                   D.2           
D          10                   D.10          
D          11                   D.11          
D          12                   D.12

If you would sort them now with ORDER BY alpha1, num1, alpha2, num2 they would be sorted as you want them. But the already "formatted" version (the Original column) cannot be sorted easily, because the parts that shall be sorted alphabetically and the parts that shall be sorted numerically are mixed together.

There is a somewhat less extensive alternative needing only one extra column where you assume no number ever goes beyond let's say 10000 and you can now replace every number (not digit!) with a zero-padded version, so 10 A-1 would become 0010A0001 (which is 0010 and A and 0001, obviously), but I don't see this being made on-the-fly in an ORDER BY statement.

But for this example, the zeropadded version (Assumption: every number < 10000):

Original      Zeropadded 
1             0001       
2             0002       
2.B3          0002B0003  
5             0005       
9             0009       
10 A-1        0010A0001  
10 A-3        0010A0003  
10 B-4        0010B0004  
10 B-5        0010B0005  
11            0011       
12            0012       
B3-43         B00030043  
B3-44         B00030043  
B3 - 48       B00030048  
B3 - 49       B00030049  
Basztowa 3    Baztowa0003
Basztowa 4    Baztowa0004
Basztowa 5    Baztowa0005
Basztowa 7    Baztowa0007
Basztowa 9    Baztowa0009
D.1           D0001      
D.2           D0002      
D.10          D0010      
D.11          D0011      
D.12          D0012

This would be sortable to your wishes with ORDER BY zeropadded.

So in the end, you probably have to sort in php or create more columns that help you sort via reformatting/sanitizing/splitting your input.

How to sort alphanumeric data in mysql?

Sign up