SQL für flexible Telefonnummern-Suche

[Stephan]

Hallo,

ich hänge gerade bei der banalen Frage welchen Platzhalter ich in einem SQL-Ausdruck für "ein beliebiges oder kein Zeichen" verwenden muss.

Was ist das Ziel?
In einer Datentabelle sind Datensätze die in einem Feld Telefonnummern enthalten. Diese Telefonnummern können dort mit beliebigen 'Trennern' vorkommen, z.B.:

030-12345
030 12345
030_12345
03012345
030-1234-5
...

gefunden werden soll aber immer die Nummer die dem von allen Nicht-Ziffernzeichen bereinigten Ausdruck entspricht, also hier 03012345.

Der SQL-Ausdruck wird in einem Makro, basierend auf einer Eingabe in einer Suchmaske zusammengesetzt, derzeitig so:

Code: Alles auswählen

Select * FROM "Firma" WHERE "Telefon" LIKE '0*3*0*1*2*3*4*5' ORDER BY "Firmenname" ASC, "Abteilung" ASC

was aber natürlich falsche Ergebnisse liefert weil "*" auch mehrere Zeichen umfassen kann. "?" hingegen erwartet IMMER ein Zeichen.

Wie lässt sich das richtig formulieren?

Eleganter wäre wohl ein SQL-Ausdruck der das Problem 'andersherum' angehen würde nämlich im Sinne:

Code: Alles auswählen

Select * FROM "Firma" WHERE <...> LIKE '03012345' ORDER BY "Firmenname" ASC, "Abteilung" ASC

Wobei dann für "<...>" ein Ausdruck benötigt würde, der quasi 'dynamisch' im SQL aus dem Feld "Telefon" in der Datentabelle alles 'virtuell' 'entfernt' was keine Ziffer ist.


Gruß
Stephan

[Stephan]

eine Ergänzung:
mit "%" hatte ich schon experimentiert, allerdings liefert das manch unerklärliche Ergebnisse.

Beispiel:

Code: Alles auswählen

Select * FROM "Firma" WHERE "Telefon" LIKE '0%7%1%6%1' ORDER BY "Firmenname" ASC, "Abteilung" ASC

liefert u.a. einen Datensatz mit Telefon 0711-93..., den ich mir nicht erklären kann.

[Stephan]

Ich bin etwas weiter mit der zweiten Variante. Es funktioniert:

Code: Alles auswählen

Select * FROM "Firma" WHERE REPLACE( REPLACE( REPLACE( REPLACE( "Telefon", '+', '' ), '/', '' ), ' ', '' ), '-', '' ) LIKE '03012345' ORDER BY "Firmenname" ASC, "Abteilung" ASC

aber wie schreibe ich das für alle nicht-numerischen Zeichen? Das hier funktioniert nicht:

Code: Alles auswählen

Select * FROM "Firma" WHERE REPLACE("Telefon", '[^0-9]', '') LIKE '03012345' ORDER BY "Firmenname" ASC, "Abteilung" ASC

Gruß
Stephan

[Toxitom]

Hallo Stephan,

ansich sollte es ganz einfach mit Reg-Ausdrücken gehen. Hängt aber von der verwendeten Datenbank (DBMS) ab.

Hast Du es mal versucht?

Code: Alles auswählen

Select * FROM "Firma" WHERE "Telefon" LIKE '^[0-9]{1,15}$' ORDER BY "Firmenname" ASC, "Abteilung" ASC

immer vorausgesetzt, die Telefonnummer ist maximal 15 Ziffern lang - ansonsten eben entsprechend anpassen.

VG
Tom

[RobertG]

Hallo Tom,

sicher, dass die Zusammenstellung von regulären Ausdrücken bei LIKE klappt? Mit der internen HSQLDB dürfte das nicht funktionieren, da LIKE nur auf Text zugreift und dort mit "_" ein Zeichen und mit "%" beliebig viele Zeichen zulässt. Es gibt dort lediglich die Möglichkeit, über den Zusatz ESCAPE dafür zu sorgen, dass das entsprechende Zeichen selbst ggf. auch gefunden werden kann.

Deine Variante gibt es z.B. bei MySQL als REGEXP bzw. RLIKE, nicht aber bei der internen HSQLDB.

Select * FROM "Firma" WHERE "Telefon" LIKE '0%7%1%6%1' ORDER BY "Firmenname" ASC, "Abteilung" ASC

liefert u.a. einen Datensatz mit Telefon 0711-93..., den ich mir nicht erklären kann.

Das liegt vermutlich daran, dass nach der 0 beliebig viele Zeichen vorkommen können, dann eine 7, dann wieder beliebig viele Zeichen und anschließend eine 1. Die nächste '1' wird wohl irgendwo weiter hinten in der Nummer vorkommen, weil vorher eine '6' stehen muss, die wir hier nicht sehen.

Die neue interne Firebird-Datenbank von LibreOffice hat hier mit SIMILAR TO entsprechende Möglichkeiten, auch reguläre Ausdrücke zu verarbeiten. Ich weiß aber nicht, ob Stephan diese DB nutzt.

Für mich liegt das Problem daran, dass die Daten eben nicht standardisiert abgelegt wurden. Das wäre die einfachste Möglichkeit gewesen, dafür ein numerisches Feld zu nutzen. Jetzt hilft wohl nur die Methode, möglichst viele Treffer über REPLACE zu erzeugen - und schnellsten die Eingabe umzustellen.

Gruß

Robert

[Toxitom]

Hey Robert,

sicher, dass die Zusammenstellung von regulären Ausdrücken bei LIKE klappt?...

ja. Aber: siehe oben.... hängt vom DBMS ab. Bei MySQL sollte es gehen, bei Prostgres wohl auch, HSQLDB weiss ich nicht - muss halt getestet werden. Stephan hat keine DB genannt -oder?

Gruss
Tom

[RobertG]

Hallo Tom,

auch in meinem (etwas alten) MySQL-Buch ist LIKE nur mit "_" und "%" sowie "\" zum Escapen bestückt. Das, was Du da vorschlägst, dürfte danach RLIKE oder REGEXP sein. Aber natürlich hast Du damit recht, dass das von dem Datenbanksystem dahinter abhängt, was denn nun möglich ist.

Gruß

Robert

[Stephan]

Hallo Tom,

die Datenbank ist die interne HSQLDB.

Dein SQL liefert leider ein leeres Abfrageergebnis. Ich verstehe Dein SQL aber auch nicht, da dort nirgends der zu suchende Ausdruck vorkommt, sondern halt (mutmaßlich) nur 15 Ziffern. Eigentlich nehme ich wegen des "^" sogar an 15 Nichtziffern.?


Gruß
Stephan

[Stephan]

Hallo Robert,

Für mich liegt das Problem daran, dass die Daten eben nicht standardisiert abgelegt wurden. Das wäre die einfachste Möglichkeit gewesen, dafür ein numerisches Feld zu nutzen.

ja, klar.

Jetzt hilft wohl nur die Methode, möglichst viele Treffer über REPLACE zu erzeugen

und die konkrete Verwendung von REPLACE geht, im vorliegenden Falle, nur über die 'geschachtelten' REPLACE-Ausdrücke wie ich sie bereits verwende?


GRuß
Stephan

[F3K Total]

Hallo Stephan,
noch ein Vorschlag, warum "säuberst" du die Nummern nicht?
z.B. über Extras/SQL... mit

Code: Alles auswählen

UPDATE "Firma" SET "Telefon" = Replace("Telefon",'/','');
UPDATE "Firma" SET "Telefon" = Replace("Telefon",' ','');
UPDATE "Firma" SET "Telefon" = Replace("Telefon",'-','')

um alle Slash, Leerzeichen und Bindestriche zu entfernen, dann wird es sicher einfacher mit der Suche.
Etwa so

Code: Alles auswählen

SELECT * FROM "Firma" where "Telefon" like '%'||:qNummer||'%'

Gruß R
EDIT: Die UPDATE Befehle hatten einen Fehler, korrigiert ...

[Stephan]

noch ein Vorschlag, warum "säuberst" du die Nummern nicht?

Weil es um ein Kundenprojekt geht und ich nicht verlässlich ausschließen kann das die Telefonnummern 'sauber' bleiben, genauer gesagt das später nicht wieder 'unsaubere' hinzukommen.
Da das Einfügen neuer Nummern auch mit Copy/Paste funktionieren muss und es zumindest 2 verschiedene Eingabeformate geben muss (1. Vorwahl-Trenner-Nummer und 3. Vorwahl-Trenner-Nummer-Trenner-Durchwahl) scheint es mir unbequem bis ergonomisch das alles bei der Eingabe abzufangen.


Gruß
Stephan

[RobertG]

Hallo Stephan,

Jetzt hilft wohl nur die Methode, möglichst viele Treffer über REPLACE zu erzeugen

und die konkrete Verwendung von REPLACE geht, im vorliegenden Falle, nur über die 'geschachtelten' REPLACE-Ausdrücke wie ich sie bereits verwende?

Ich wüsste keine andere Möglichkeit. Aber aufgepasst mit dem "+". Steht das zu Beginn, so musst Du stattdessen ein "00" da einfügen. Da musst Du dann wohl zuerst einmal mit CASE WHEN operieren, um das führende "+" durch "00" ersetzen und dann über Replace alle anderen Zeichen rauszufiltern

Gruß

Robert

[Stephan]

Hallo Robert,

Aber aufgepasst mit dem "+". Steht das zu Beginn, so musst Du stattdessen ein "00" da einfügen. Da musst Du dann wohl zuerst einmal mit CASE WHEN operieren, um das führende "+" durch "00" ersetzen und dann über Replace alle anderen Zeichen rauszufiltern

Der Grund für diesen Hinweis ist aber rein die Richtigkeit der Telefonnummer, oder gibt es einen technischen Grund, den ich übersehe?


Gruß
Stephan

[RobertG]

Hallo Stephan,

ja, das geht nur um die korrekte Nummer. Ich kann mir so eine Suche nur schlecht vorstellen. Wenn das "+" und die "00" nicht da sind, dann kann das Ganze in der Suchfunktion doch so aussehen, dass es sich um eine örtliche Nummer handelt, oder?

Gruß

Robert

[Stephan]

Hallo Robert,

Wenn das "+" und die "00" nicht da sind, dann kann das Ganze in der Suchfunktion doch so aussehen, dass es sich um eine örtliche Nummer handelt, oder?

ja, würde ich sagen und ich verstehe jetzt worauf Du mich hinweisen willst. Meine momentane Antwort ist kurz aber nicht sehr befriedigend:

Ich habe noch nicht darüber nachgedacht wie ich das löse.

Da ich Durch Deinen Hinweis sehe das dieses Problem eine Lösung braucht, werde ich da etwas tun müssen und falls ich es nicht vergesse, schreibe ich meine Lösung noch später hier in den Thread rein.


Gruß
Stephan