W jaki sposób skonstruować order by, żeby uzyskać taki efekt?

Rozwiązanie

Próbowałem już wielu rzeczy, ale bezskutecznie. Zawsze znajdzie się jakiś buntownik, który nie chce stać tam gdzie powinien. Mam kolumne, w której mogą być różne wartości w przeróżnych kombinacjach i nie potrafię sobie poradzić z posortowaniem tego.

Przykładowe wartości:

[0-9]
[0-9] - [a-zA-Z]
[a-zA-Z] - [0-9]
[a-zA-Z]-[0-9]
[a-zA-Z].[0-9]
[a-zA-Z] [0-9]

Dla ułatwienia stworzyłem SQL Fiddle.

Przykładowy output:

1
2
2.B3
5
9
10 A-1
10 A-3
10 B-4
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

Jeżeli z jakiś powodów osiągnięcie czegoś takiego może być niemożliwe, to jakie sortowanie w takim wypadku proponujecie?

SELECT name,  (name = '0') boolZero, (name+0 > 0) boolNum 
FROM `realestate` 
ORDER BY boolZero DESC, boolNum DESC, (name+0), name 

Ewentualnie pokombinuj z tym, nie działa to poniżej dokładnie jakbyś tego potrzebował:

SELECT * FROM `realestate` ORDER BY  if(name RLIKE '[[:digit:]]+',1,2), name 

Rozwiązaniem problemu jest znormalizowanie nazw nieruchomości przez usunięcie znaków niealfanumerycznych i left pad każdej liczby w ciągu X ilością 0. Ja założyłem, że pojedyńcza liczba nie będzie większa niż 10 000.

Migracja, która przepisze nazwy nieruchomości do osobnej kolumny, po której będziemy sortować:

$data = $this->fetchAll('SELECT id, name FROM realestate');
        $when = [];

        foreach ($data as $row) {
            // remove non-alphanumeric
            $name = preg_replace("/[^A-Za-z0-9 ]/", ' ', $row['name']);
            // remove double spaces
            $normalizedName = preg_replace('!\s+!', ' ', $name);

            // left pad every number
            $normalizedName = preg_replace_callback('!\d+!', function ($matches) {
                $number = array_shift($matches);
                $lpadded = str_pad($number, 4, '0', STR_PAD_LEFT);
                return $lpadded;
            }, $normalizedName);

            // remove all whitespaces
            $zeropaddedName = preg_replace('/\s+/', '', $normalizedName);

            $when[] = "WHEN id = {$row['id']} THEN '$zeropaddedName'";
        }

        $this->execute('ALTER TABLE `realestate` ADD COLUMN `zeropadded_name` VARCHAR(256) NOT NULL AFTER `name`');

        $this->execute("UPDATE `realestate` SET `zeropadded_name` = CASE " . implode(' ', $when) . " END");

Teraz każdorazowo przy edycji nieruchomości wystarczy skleić nową nazwę na zasadzie B3 - 48 => B00030048.
Sortujemy

SELECT * FROM realestate ORDER BY zeropadded_name