Sortieren von Hausnummern: 1,1a,1b,2... usw

[Steffen]

Hi,

ich habe eine Datenbank mit Adressen.

+varchar+varchar+
|Strasse+Hausnr.|
+-------+-------+

Das Datenbank Schema kann bzw. darf ich nicht ändern.
In der Spalte Hausnummer gibt es nun z.B. folgenden Einträge:
1,2,3,..., 20,21a,...

bei einer SELECT... ORDER BY Strass, Hausnr Abfrage wird nun z.B. die "3" _nach_ der "20" ausgegeben, das ist ja auch die "richtige" lexikalische Ordnung, aber eben nicht die logische.

Hat jemnand eine Idee, wie man die "logische" Ordnung (1,2,3,...,20,21,...) ohne Änderung an der DB ausgeben?

Gruß
Steffen

[Ilja]

yo,

Das Datenbank Schema kann bzw. darf ich nicht ändern.

grundsätzlixh sollte man die hausnummer und den zusatz voneinander in zwei spalten trennen. die probleme, die du jetzt hast, spiegeln dies gut wieder und wäre für dich ja eine gute argumentation, es doch zu ändern. aber wie dem auch sei, du musst eben über eine funktion die zahlen und die buchstaben voneinander trennen und dann danach sortieren. es gibt sicherlich mehrere möglichkeiten dazu, eine wäre mit regulären ausdrücken. eine andere möglichkeit besteht darin, nach dem ersten zeichen zu suchen, dass keine zahl ist und dann mit substring sie dort abzuschneiden. das setzt aber vorraus, dass die zahlen immer links stehen. welche funktionen es genau sind, hängt von deiner version und dem dbms ab. wie auch immer, am ende musst du die gewonnen Zahlen als INTEGER casten.

Ilja

[Alexander (HH)]

Moin Moin!

Such mal nach "natural sort".

Alexander

[Tom]

Hello,

es hängt von Deinem DBMS ab, ob dieses die Maßnahme zulässt, aber die Lösung kann sein:

1
     2
     3
    20
   21a

stelle die Nummern rechtsbündig, indem Du sie links auf die Spaltenbreite mit einem neutralen Element (Leerzeichen) auffüllst.

Das geht mit einem Update.
Dann müsstest Du nur noch einen Insert-Trigger definieren, der das in Zukunft immer automatisch macht.

Liebe Grüße aus Syburg

Tom vom Berg

[MudGuard]

Hi,

1
     2
     3
    20
   21a

Und wie soll dann sortiert werden?
Wenn Du das textuell sortieren läßt, kommt 22 vor 21a ...

cu,
Andreas

[Tom]

Hello,

1
      2
      3
     20
    21a

Und wie soll dann sortiert werden?
Wenn Du das textuell sortieren läßt, kommt 22 vor 21a ...

zuende denken wird das Steffen schon selber müssen

Liebe Grüße aus Syburg

Tom vom Berg

[Sven Rautenberg]

Moin!

Hello,

es hängt von Deinem DBMS ab, ob dieses die Maßnahme zulässt, aber die Lösung kann sein:

1
     2
     3
    20
   21a

stelle die Nummern rechtsbündig, indem Du sie links auf die Spaltenbreite mit einem neutralen Element (Leerzeichen) auffüllst.

Das geht mit einem Update.
Dann müsstest Du nur noch einen Insert-Trigger definieren, der das in Zukunft immer automatisch macht.

Ich weiß nicht, ob das die schlaueste Lösung ist.

Zum einen: Was ist mit der Sortierung dieser Hausnummern:

20
 20a
  21

Rechtsbündigkeit mit Leerzeichen herzustellen hilft nicht automatisch, es ist eher ein "zentrieren".

Zweitens: Ein INSERT-Trigger allein reicht ja auch nicht, es muss mindestens noch ein UPDATE-Trigger dazukommen, und außerdem noch irgendeine Art von View, der die Leerzeichen beim Auslesen wieder eliminiert.

Außerdem: Trigger sind pro Tabelle definiert, nicht pro Spalte. Und es ist nach meinem Gefühl keine so wahnsinnig gute Idee, die Datenhaltungslogik getrennt sowohl in der abfragenden Applikation als auch plötzlich als Trigger in der Datenbank aufzubewahren.

- Sven Rautenberg

[Tom]

Hello,

Ich weiß nicht, ob das die schlaueste Lösung ist.

Und wieso schlägst Du sie dann selber vor?
Bisschen weiterdenken muss Steffen sicherlich selber noch.

Zweitens: Ein INSERT-Trigger allein reicht ja auch nicht, es muss mindestens noch ein UPDATE-Trigger dazukommen,

Das ist wahr.

und außerdem noch irgendeine Art von View, der die Leerzeichen beim Auslesen wieder eliminiert.

ja, der heißt dann ltrim(hausnummer) oder so ähnlich

Außerdem: Trigger sind pro Tabelle definiert, nicht pro Spalte. Und es ist nach meinem Gefühl keine so wahnsinnig gute Idee, die Datenhaltungslogik getrennt sowohl in der abfragenden Applikation als auch plötzlich als Trigger in der Datenbank aufzubewahren.

Der richtige Ort wären die Stored Procedures, die für das Insert oder Update zuständig sind.
In professionellen Datanbanklösungen lässt man schließlich keinen Client direkt an die Tabellen heran!

Ersatzweise sind die Trigger für Insert und Update nutzbar.

Die Sortierung ist Sache der Datenbank, folglich ist die Datenrichtigstellung zum Zwwecke der Sortierung auch ihre Sache und nicht die Sache einer API oder eines Clients.

Liebe Grüße aus Syburg

Tom vom Berg

[Sven Rautenberg]

Moin!

In der Spalte Hausnummer gibt es nun z.B. folgenden Einträge:
1,2,3,..., 20,21a,...

bei einer SELECT... ORDER BY Strass, Hausnr Abfrage wird nun z.B. die "3" _nach_ der "20" ausgegeben, das ist ja auch die "richtige" lexikalische Ordnung, aber eben nicht die logische.

In PHP gäbe es die Funktion strnatcmp() für einen Stringvergleich, der Zahlen als Wert berücksichtigt, und nicht als String.

Leider bietet MySQL solch eine Vergleichsfunktion für die Sortierung nicht mit an. Du müsstest dir also behelfen, indem du alle Hausnummern so "vorbehandelst", dass die Stringsortierung zu den gewünschten Ergebnissen führt. Das ist leider schwieriger, als es sich anhört.

Denn einerseits: Die vorbehandelten Hausnummern können nicht in einer weiteren Tabellenspalte gespeichert werden, weil du nichts am Schema ändern willst. Andererseits: Für jede Abfrage die Hausnummern dynamisch mit komplexen Stringfunktionen zu behandeln, nur um die Sortierung richtig hinzubekommen, dürfte ganz schön auf die Performance gehen.

Jedenfalls: Die Vorgehensweise wäre, die Hausnummern erst einmal in zwei Teilen zu behandeln: Den Zahlenanteil und den eventuell folgenden Buchstabenanteil. Der Zahlenanteil wird mit führenden Nullen oder mit führenden Leerzeichen aufgefüllt, und zwar auf mindestens die Länge, die der Zahlenanteil der längsten Hausnummer lang ist. Der Buchstabenanteil kommt dann wieder hinten dran...

Die Sortierung vergleicht dann "0003" mit "0020" und "0020a", und wird die richtige Reihenfolge herstellen, auch mit nur einem schlichten Stringvergleich.

Ich bastel' dir aber die Funktion nicht hin, die diese Transformation durchführt.

Ausserdem gebe ich zu bedenken, dass Adressen ein höchst merkwürdiges Konstrukt sind. Ich verweise nur mal auf die Quadratestadt in Mannheim, oder auf die Möglichkeit, dass eine Adresse "Müllerstraße 43-47" lauten kann.

Für die Merkwürdigkeiten von Adressmaterial eine allumfassende Sortierlösung zu bauen kann durchaus zu einer sehr umfangreichen Aufgabe werden. Ich vermute mal, deine Datenbank ist schon etwas gefüllt, so dass du mal nachgucken könntest, was sich da schon alles angesammelt hat, um einen Eindruck zu bekommen.

- Sven Rautenberg

[Steffen]

Ich habe es schon geahnt: Das wird ein größere Brocken Handarbeit. Naja, vielen Dank an alle die sich Gedanken gemacht haben.

So long,
Steffen

[Tom]

Hello,

Ich habe es schon geahnt: Das wird ein größere Brocken Handarbeit.

Das kommt auf das DBMS an, das Du verwendest
Wenn es Trigger kann und userdefined Functions, dann schreib Dir diese Umwandlungsfunktion, die Sven beschrieben hat und benutze sie im trigger und im einmaligen Update-Statement.

feststellen, wieviele Zeichen von links an Ziffern sind, also wo das erste nicht numerische Zeichen kommt und dann links mit Leerzeichen auf eine feste Stellenzahl auffüllen

Naja, vielen Dank an alle die sich Gedanken gemacht haben.

Gerne geschehen.

Liebe Grüße aus Syburg

Tom vom Berg

[Alexander (HH)]

Moin Moin!

Ausserdem gebe ich zu bedenken, dass Adressen ein höchst merkwürdiges Konstrukt sind. Ich verweise nur mal auf die Quadratestadt in Mannheim, oder auf die Möglichkeit, dass eine Adresse "Müllerstraße 43-47" lauten kann.

Was noch harmlos ist. Ich hab vor etwa zehn Jahren mal das fragwürdige Vergnügen gehabt, eine Kontakt-Datenbank auf ein neues System zu übertragen. Natürlich waren beide Systeme nicht normalisiert. Ein Nummernbereich war da für Hausnummern noch das harmloseste. Lustig wird es z.B., wenn große Fabrikflächen eines Unternehmens mit einer einzigen Hausnummer aufgegeben und für viele neue Unternehmen "recycled" werden. Da kommen dann Konstruktionen wie "Musterstraße 42 Block III Haus 5" zusammen.

Für die Merkwürdigkeiten von Adressmaterial eine allumfassende Sortierlösung zu bauen kann durchaus zu einer sehr umfangreichen Aufgabe werden. Ich vermute mal, deine Datenbank ist schon etwas gefüllt, so dass du mal nachgucken könntest, was sich da schon alles angesammelt hat, um einen Eindruck zu bekommen.

Ich würde damit anfangen, alle Hausnummern zu suchen, die nicht auf das Pattern /^\d+[a-zA-Z]?$/ passen. Da dürften einige Scan- und Tippfehler und das eine oder andere Monster à la "42 Block III Haus 5" zusammenkommen.

Alexander

[MudGuard]

Hi,

Für die Merkwürdigkeiten von Adressmaterial eine allumfassende Sortierlösung zu bauen kann durchaus zu einer sehr umfangreichen Aufgabe werden. Ich vermute mal, deine Datenbank ist schon etwas gefüllt, so dass du mal nachgucken könntest, was sich da schon alles angesammelt hat, um einen Eindruck zu bekommen.

Schön sind auch Konstrukte wie
Straße 5 7 (Straße = "Straße 5", Hausnummer = "7")

Es gibt auch Hausnummern nach dem Schema 1 2/3, gerne wird auch noch ein Stockwerk angegeben (7 IV) oder Zusätze wie Rgb (Rückgebäude) usw.

Um es kurz zusammenzufassen: für Straße/Hausnummer gibt es nichts, was es nicht gibt.

cu,
Andreas

[Ilja]

yo,

Leider bietet MySQL solch eine Vergleichsfunktion für die Sortierung nicht mit an.

habe ich was verpasst oder woher weisst du, dass er in diesem falle mysql meint ?

Ilja

[Sven Rautenberg]

Moin!

»» Leider bietet MySQL solch eine Vergleichsfunktion für die Sortierung nicht mit an.

habe ich was verpasst oder woher weisst du, dass er in diesem falle mysql meint ?

Du hast nichts verpasst. Weder war von PHP die Rede, noch von MySQL, das sind alles geratene Annahmen, die ausnutzen, dass die Trefferwahrscheinlichkeit in diesem Forum relativ hoch ist.

Wenn's falsch war, werden wir das vermutlich gesagt bekommen. :)

- Sven Rautenberg

[Ilja]

yo,

eder war von PHP die Rede, noch von MySQL, das sind alles geratene Annahmen, die ausnutzen, dass die Trefferwahrscheinlichkeit in diesem Forum relativ hoch ist.

jo, gut getippt. wie auch immer, ich würde wie du den zahlenteil und den zusatz voneinander trennen und auch die performance sollte darunter nicht sonderlich leiden. schließlich wird der teil ja nicht dazu benutzt zu selektieren. sortieren musst du eh über alle ergebnisdatensätze. allerdings ist mir noch nicht klar, warum ihr die werte immer bis zu einer spezifischen länge auffüllen wollt. wenn ich schon zahlen und andere zeichen voneinander trenne, dann caste ich es doch lieber als zahl und sortiere danach. oder spricht da was dagegen ?

Ilja

[Steffen]

Hi

habe ich was verpasst oder woher weisst du, dass er in diesem falle mysql meint ?

Hatte ich zwar nicht erwähnt, aber ich verwende mysql

Steffen

[hotti]

hi,

Hat jemnand eine Idee, wie man die "logische" Ordnung (1,2,3,...,20,21,...) ohne Änderung an der DB ausgeben?

bissl Perl:

  
my @ar = qw(1 11 12 3 33 24 2a 2b 33a 33b 1a 35b 100c 3c 11a 11b 12c 222);  
  
foreach my $h( sort { sh($a) <=> sh($b) } @ar){  
	print "$h\n";  
}  
  
sub sh{  
	my $h = shift;  
	$h =~ /^(\d+)(.*)$/;  
	return ($1 * 255) + (ord $2); # Wichtung setzen	  
}  

1
1a
2a
2b
3
3c
11
11a
11b
12
12c
24
33
33a
33b
35b
100c
222

Bedingung ist o.g. Syntax der Hausnummern. Ggf. alles auf LowerCase umschießen.

Zigarre,
Hotti

[Patrick Andrieu]

Hallo hotti!

my @ar = qw(1 11 12 3 33 24 2a 2b 33a 33b 1a 35b 100c 3c 11a 11b 12c 222);

foreach my $h( sort { sh($a) <=> sh($b) } @ar){
print "$h\n";
}

sub sh{
my $h = shift;
$h =~ /^(\d+)(.*)$/;
return ($1 * 255) + (ord $2); # Wichtung setzen
}

> Bedingung ist o.g. Syntax der Hausnummern. Ggf. alles auf LowerCase umschießen.  
  
Du kennst nicht die [Schwartz'sche Transformation](http://www.stonehenge.com/merlyn/UnixReview/col64.html)? [ ] Doch. [ ] Nie gehört.  
  
C:\>perl -w  
~~~perl
my @ar = qw(1 11 12 3 33 24 2a 2b 33a 33b 1a 35b 100c 3c 11a 11b 12c 222);  
@ar = map { $_->[0] }  
      sort { $a->[1] <=> $b->[1] }  
      map { [ $_, ( /(\d+)/ )[0] ] } @ar;  
foreach (@ar) {print $_,"\n";}

^Z
1
1a
2a
2b
3
3c
11
11a
11b
12
12c
24
33
33a
33b
35b
100c
222

May the (Randal L.) Schwartz be with you!

Zigarre,

Gerne!

Viele Grüße aus Frankfurt/Main,
Patrick