How to sort alphanumeric data in mysql?
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.
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.