Kamil Latosinski - 1 year ago 48

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 Source

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.