Sortier Horror

[sutterp]

In einer Adressen Tabelle werden Strassen Addressen und Orte in separaten Kolonnen abgespeichert. Die Strassen Adressen Kolonne sieht etwa folgendermassen aus

1 Bebich Drive
2 Bebich Drive
10 Bebich Drive
20 Bebich Drive
100 Bebich Drive
PO Box 1
PO Box 10
PO Box 100
PO Box 2

Wenn man die Tabelle nach Strassennamen sortiert, wird gemaess String sortier Regeln alle Strassen Addressen welche mit 1 anfange zusammengefasst, dann alle die mit 2 anfangen, etc.

Gibt es eine einfache Moeglichkeit, mittles regexp etwas Sinn in das ganxe zu bekommen. Dies muesste doch moeglich sein und ist wahrscheinlich auch schon gemacht worden.

Wer erleuchtet mich?

Danke

Peter

[jan61]

Moin,

mir ist unklar, was Du willst. Wie soll das Ergebnis aussehen? Welche DB setzt Du ein?

Jan

P.S.: Write in english if it's easier for you.

[sutterp]

Danke Jan,

Die Datenbank ist Mysql V4.

Die Daten der Strassen Adresse kommen von einem Buchhaltungs Paket welches unter Windows laueft und werden mittels eines Interfaces eingespielt. Das sortierte Resultat sollte aufsteigend Strassenname, dann aufsteigend Hausnummer fuer Strassen Adressen, oder aufsteigend Post Box, dann aufsteigend Box Nummer wenn die Adresse eine PO Box ist.

Z.B. Erwuenschtes Resultat
1 Bebich Drive
2 Bebich Drive
10 Bebich Drive
20 Bebich Drive
100 Bebich Drive
PO Box 1
PO Box 2
PO Box 10
PO Box 20
PO Box 100
1 Stafford Way
2 Stafford Way
10 Stafford Way
20 Stafford Way
100 Stafford Way

Resultat mit order by StreetAddress
1 Bebich Drive
1 Stafford Way
10 Bebich Drive
10 Stafford Way
100 Bebich Drive
100 Stafford Way
2 Bebich Drive
2 Stafford Way
20 Bebich Drive
20 Stafford Way
PO Box 1
PO Box 10
PO Box 100
PO Box 2
Mit order by streetaddress wird die gesamte Adresse als String behandelt, und ist eigentlich nicht das was der Benuetzer erwartet.

Ich weiss nicht, wie lange die vorstehende oder nachstehende Nummer ist, kann bis zu 5 stellen sein. Im Deutschsprachigen Raum steht die Hausnummer hinter dem Strassennamen, dies vereinfacht die sortiererei erheblich (Strassenname 123 oder Postfach 123). Hier kommt als zusaetzliche Schwierigkeit hinzu, dass bei Strassenadressen die Hausnummer vorstehend ist, bei Postfaechern aber nachstehend (123 Strassenname oder PO Box 123).

Um den Sort Begriff zu erstellen, sollte der numerische Teil falls er vorne steht ignoriert werden und hinten als integer angesetzt werden (Strassenname oder Postfach als String, Hausnummer oder Postfach als Integer). Dies sollte mit
order by regexp(), regexp(), regexp() oder aehnlichem
irgendwie moeglich sein, aber ich krieg das nicht ganz hin.

Danke

Peter

[Christoph]

Wenn die Nummern in einem eigenen Feld gespeichert sind, dann kannst Du numerisch sortieren, indem Du sie mit to_number() in Zahlen umwandelst.

Wenn sie nicht in einem eigenen Feld gespeichert sind, Du sie aber separat als Sortierkriterium haben willst, dann musst Du die Tabelle erstmal in erste (!) Normalform bringen, d.h. die zusammengesetzten Felder in einzelne Attribute zerlegen.

[jan61]

Moin,


...Die Datenbank ist Mysql V4....
Um den Sort Begriff zu erstellen, sollte der numerische Teil falls er vorne steht ignoriert werden und hinten als integer angesetzt werden (Strassenname oder Postfach als String, Hausnummer oder Postfach als Integer). Dies sollte mit order by regexp(), regexp(), regexp() oder aehnlichem irgendwie moeglich sein, aber ich krieg das nicht ganz hin.

Ich sehe das ähnlich wie Christoph. Du erleichterst Dir die Arbeit enorm, wenn Du Dir die Daten so aufbereitest, dass Du auch sinnvoll sortieren kannst. Eine Variante hatte er schon genannt, allerdings würde ich die Daten redundant halten (Haus- oder PO-Box-Nummer zusätzlich zur Strasse), damit die Strasse/PO-Box in der Original-Form angezeigt werden kann.

Im MySQL 4 gibt es nach meiner Doku noch gar keine Funktionen für regex, das sind alles Operatoren, die nur 0 oder 1 zurückliefern und nur in WHERE-Bedingungen sinnvoll genutzt werden können (4.0.18). Außerdem brauchst Du eher sowas wie regexp_replace, das ich nur aus Oracle kenne (wo man auch einen funktionsbasierten Index erstellen kann, der solche Sortierungen erheblich vereinfacht).

Mein Vorschlag weicht ein wenig ab: Baue eine zusätzliche Spalte "sorted_address", in der die Daten wie folgt abgelegt werden:
PO Box 00001
Bebich Drive 00001
PO Box 00100
Stafford Way 00010
Bebich Drive 00002

Damit kannst Du dann prima sortieren (und brauchst Dazu nur 1 Spalte). Auf die Spalte kannst Du dann bei Bedarf auch noch einen Index legen. Wenn Du das über einen Insert-/Update-Trigger realisierst (sowas geht doch in MySQL 4, oder? - so langsam haben die doch wohl das eine oder andere Feature eines richtigen DBMS abgekupfert?), dann spürst Du in der Anwendung gar nichts davon.

Jan

[sutterp]

Vielen Dank an Alle die geantwortet haben.

Ich kann ja leider die Tabellenstruktur nicht veraendern, diese wird ja von einem Buchhaltungspaket erstellt. Jedesmal die Tabelle zu duplizieren und die Adresse auseinandernehmen und in verschiedenenen Feldern abspeichern nur damit ich sortieren kann, ist fuer meinen Begriff ein Overkill.

Ich glaube ich habe eine Loesung gefunden welche das erwuenschte Resultat produziert, und zwar mittels regexp und substring Funktionen in der Order Clause.


order by
if(Address regexp '^[0-9]+ ',substring(Address,locate(" ",Address)+1), substring_index(Address," ",2)),
if(Address regexp '^[0-9]+ ',abs(substring_index(Address," ",2)), abs(substring_index(Address," ",-1))),
Locality

Das erste if extrahiert den Strassennamen wenn die Adresse vorstehende Ziffern hat, oder das Postfach wenn dies nicht der Fall ist.

Das zweite if extrahiert die vorstehende Hausnummer wenn die Adresse vorstehende Ziffern hat, oder die nachstehende Postfachnummer wenn dies nicht der Fall ist.

Wenn die Adresse keine vorstehenden Ziffern enthaelt, ist das Resultat von abs(substring_index(Address," ",2)) welches in diesem Fall ein String ist, 0, was fuer den Sortbegriff richtig ist.

Was haelt Ihr davon?

Peter

[jan61]

Moin,


...Was haelt Ihr davon?

genau deshalb hasse ich MySQL - hier wird "My"SQL-Code erzeugt, der mit nichts und niemand kompatibel ist. Was hat eine Ablaufsteuerung (if - then - else) in einer SQL-Abfrage zu suchen? Nichts! Für sowas gibt es Funktionen, SQL-Prozeduren usw. SQL ist eine Abfragesprache, keine Programmiersprache.

Ich habe bisher noch keine dieser katastrophalen Abfragen gesehen, die nicht durch ein sauberes (sauber im Sinne von "benutzbar", nicht unbedingt im Sinne von "der reinen Lehre entsprechend") DB-Design verhindert werden könnten - und dann laufen die Abfragen nach meiner Erfahrung auch immer schneller und resourcenschonender ab.

Warum darfst Du das Orignal-DB-Design nicht erweitern? Es ist doch kein Akt, zusätzliche Tabellen zu generieren und (per Trigger, Procedure u. ä. zu füllen). Wenn Dir der Hersteller das verbietet, dann hat er einen grundsätzlichen Vorteil von SQL-DBs nicht verstanden oder will einfach nur extra Kohle für seine Reports kassieren.

Der Aufbau einer OLAP-DB aus Daten einer OLTP-DB ist nichts Neues oder Ungewöhnliches. Sowas gehört zu jeder Anwendung, die Analysen oder Reports aus einer Warenwirtschaft ziehen will (oder um es hochgestochen auszudrücken, zum "Data Mining").

Jan

[sutterp]

Was hat eine Ablaufsteuerung (if - then - else) in einer SQL-Abfrage zu suchen? Nichts! Für sowas gibt es Funktionen, SQL-Prozeduren usw. SQL ist eine Abfragesprache, keine Programmiersprache.
Richtig, gebe Dir recht!

dann laufen die Abfragen nach meiner Erfahrung auch immer schneller und resourcenschonender ab.
Auch richtig, gebe Dir recht!

dann hat er einen grundsätzlichen Vorteil von SQL-DBs nicht verstanden oder will einfach nur extra Kohle für seine Reports kassieren
Oder, nicht dann. Kohle, mehr Kohle, noch mehr Kohle, je mehr und je schneller, je lieber. Hersteller werden taeglich geiziger und geldgieriger. Der Support Kontrakt mit dem Hersteller verbietet Aenderungen in der Datenbank. Das ganze kommt halt aus einem Paket welches unter Microsoft Windows laeuft. Ich muss halt damit leben was machbar ist. Hatte schon Aerger genug als ich die Datenbank von der Windows Maschine auf ne Linuxmaschine transferrierte.

Zumindest habe ich einen Fix der im Augenblick laueft. Wenn ich einmal genuegend Zeit eruebrigen kann, werde ich wahrscheinlich mal ein Extraktionsprogramm schreiben welches eine neue Tabelle erstellt die eine 'saubere' Loesung darstellt.

Peter