Unterschiedlich Formatierte Telefonnummern abgleichen

[LastBoyScout]

Habe hier eine MySQL- Datenbank in der unter anderem Telefonnummern gespeichert sind. Bei diesen ist Vorwahl und Rufnummer mit einem Schrägstrich getrennt und eine evtl. Durchwahl durch einem Bindestrich, z.B. 012345/6789 bzw. 012345/6789-0

Nun sollen anhand der Rufnummer die dazugehörigen Datensätze ermittelt werden. Das Problem ist nur, das der Suchstring wie folgt Formatiert ist: [phone]

Wie kann ich diese unterschiedlich formatierten Daten abgleichen? Auf Reguläre Ausdrücke schaue ich immer wie das berühmte Schwein ins Uhrwerk :-(

Besten Dank schon mal für eure Unterstützung.

Matthew

[Henry]

Hallo LastBoyScout,

Habe hier eine MySQL- Datenbank in der unter anderem Telefonnummern gespeichert sind. Bei diesen ist Vorwahl und Rufnummer mit einem Schrägstrich getrennt und eine evtl. Durchwahl durch einem Bindestrich, z.B. 012345/6789 bzw. 012345/6789-0

keine gute Wahl, das so zu speichern.

Nun sollen anhand der Rufnummer die dazugehörigen Datensätze ermittelt werden. Das Problem ist nur, das der Suchstring wie folgt Formatiert ist: [phone]

Du hättest jetzt mehrere Ansätze zur Verfügung. Einer wäre schon mal die DB-Konstruktion zu ändern, wenn du das kannst und möchtest.

Bsp.

|landprefix|ortsprefix|nummer|durchwahl|

Das ändert natürlich nichts daran, dass du dennoch die Sucheingabe modifizieren musst, erleichtert es aber. Wenn ich das richtig verstehe, ist auch kein Verlass darauf, dass die Sucheingabe immer exakt so ist: [phone]? Auf jeden Fall, selbst wenn, solltest du so eine Eingabe sowieso immer prüfen und daher die Zahlen finden und gegebenenfalls aufteilen.

Auf Reguläre Ausdrücke schaue ich immer wie das berühmte Schwein ins Uhrwerk :-(

Es ist da auch nicht unbedingt Regex für nötig auch Stringfunktionen sind geeignet, aber hier mal ein Link wo du Ansätze für beides hier im Forum findest. Das ist zwar nicht die gleiche Frage, wie deine, die Ansätze aber. Wenn die Ansätze daraus zu komplex sind, bitte nochmal melden.

Gruss
Henry

[LastBoyScout]

Hi Henry,

keine gute Wahl, das so zu speichern.

Hab die Daten ja nicht eingegeben... bin aber schon froh, das zumindest alle im selben Format sind.

Du hättest jetzt mehrere Ansätze zur Verfügung. Einer wäre schon mal die DB-Konstruktion zu ändern, wenn du das kannst und möchtest.

Das habe ich auch schon in Erwägung gezogen. Das Problem ist nur das die Nummern auch noch lesbar bleiben sollen... sprich Vorwahl, Rufnummer und Durchwahl anhand eines Zeichen voneinander getrennt sind. E.146 klatsch ja alles zusammen!?

Wenn ich das richtig verstehe, ist auch kein Verlass darauf, dass die Sucheingabe immer exakt so ist: [phone]? Auf jeden Fall, selbst wenn, solltest du so eine Eingabe sowieso immer prüfen und daher die Zahlen finden und gegebenenfalls aufteilen.

Der Suchstring ist in Microsoft´s Kanonischen Adressformat für Telefonnummern und immer gleich.

Es ist da auch nicht unbedingt Regex für nötig auch Stringfunktionen sind geeignet

Hatte gehofft evtl. die Suche direkt von mysql über die DB-Abfrage ausführen zu können, etwa mit REGEXP?

Das Problem ist natürlich auch das bei Suche mit einer Durchwahl z.B. [phone] auch ein evtl. vorhandener Datensatz der Zentrale 012345/6489-0 ausgeliefert werden soll, sofern kein Eintrag zur Durchwahl 012345/6489-99 vorhanden ist!?

[Auge]

Hallo

Du hättest jetzt mehrere Ansätze zur Verfügung. Einer wäre schon mal die DB-Konstruktion zu ändern, wenn du das kannst und möchtest.

Das habe ich auch schon in Erwägung gezogen. Das Problem ist nur das die Nummern auch noch lesbar bleiben sollen... sprich Vorwahl, Rufnummer und Durchwahl anhand eines Zeichen voneinander getrennt sind. E.146 klatsch ja alles zusammen!?

Es war ja auch nicht die Rede davon, eine lesbare Form in der Datenbank zu löschen, sondern durch eine Spalte mit normalisierter Rufnummer zu ergänzen. Damit hast du eine Spalte in menschenlesbarer Form und eine, die für die maschinelle Verarbeitung geeignet ist. Somit müpsstest du „nur noch“ eine Sucheingabe normalisieren und mit der maschinenspalte abgleichen. In der (folgenden) Ausgabe kannst du ja die menschenlesbare Spalte verwenden.

Tschö, Auge

[LastBoyScout]

Es war ja auch nicht die Rede davon, eine lesbare Form in der Datenbank zu löschen, sondern durch eine Spalte mit normalisierter Rufnummer zu ergänzen.

An der Datenbank kann ich keine Änderungen vornehmen! Die Rufnummern selber könnte ich hingegen neu Formatieren und dies auch für zukünftige Eingaben übernehmen, allerdings eben unter bereits kommunizierter Bedingung das diese danach noch immer Lesbar sind. Die Frage dazu ist also welches Format (auser E.146) ist dazu geeignet, insbesondere im Hinblick auf eine mögl. Durchwahl?

[Gunnar Bittersmann]

@@LastBoyScout

Das habe ich auch schon in Erwägung gezogen. Das Problem ist nur das die Nummern auch noch lesbar bleiben sollen... sprich Vorwahl, Rufnummer und Durchwahl anhand eines Zeichen voneinander getrennt sind. E.146 klatsch ja alles zusammen!?

Die Telefonnummern sollen bei der Ausgabe menschenlesbar sein. Bei der Verarbeitung sollen sie maschinenlesbar sein. Wenn dabei Trennzeichen stören, dann weg damit.

• Nutzereingabe: 089/32 16 8
• in der Datenbank steht: [phone] (Landeskennzahl vorneweg; sämtliche Trennzeichen entfernt)
• Formatierung bei der Ausgabe: [phone] (Algorithmus erkennt +49 als Landeskennzahl und 89 als Ortsnetzkennzahl)

Dazu muss natürlich eine Liste der Landeskennzahlen vorliegen, damit der Algorithmus weiß, nach der wievielten Stelle die Landeskennzahl endet. Und für die einzelnen Länder (zumindest für einige) eine Liste der Ortsnetzkennzahlen. Das sollte aber nicht das Problem sein. Und der Algorithmus dürfte es besser wissen als Nutzer, an welcher Stelle Ortsnetzkennzahl und Teilnehmerrufnummer zu trennen sind, und auch eine Eingabe von (0893) 2168 führt letztendlich zur richtigen Ausgabe.

Wenn man die Lesbarkeit noch weiter verbessern will, gruppiert man die Teilnehmerrufnummer noch durch dünne Leerzeichen U+202F: $$+49;89;32,16,8$$ (+49 89 32 16 8)

LLAP 🖖

[Rolf B]

Hallo Gunnar,

Dazu muss natürlich eine Liste der Landeskennzahlen vorliegen, damit der Algorithmus weiß, nach der wievielten Stelle die Landeskennzahl endet. Und für die einzelnen Länder (zumindest für einige) eine Liste der Ortsnetzkennzahlen.

Hm, tja. Ich betreue ja auch die Telefonie-Routingplattform bei meinem Brötchenfinanzierer; und genau das Problem hatte ich da. Ich habe derzeit nur die Landeskennzahlen und die deutschen ONKZ, das ist schon eine Menge Zeugs und eine Suche "finde ich in der DB ein Präfix meiner Nummer" muss man geschickt aus UNIONs zusammensetzen, sonst ist sie eine Tablescan-Pest und sehr langsam. Und man muss regelmäßig prüfen, ob die Bundesnetzagentur was an den Nummern dreht.

Falls auch die Landeskennzahl +1 erlaubt ist, braucht man auch noch den Alternativ-Algorithmus für den NANP; da ist wegen der fixen Area Code Längen keine Präfixsuche nötig. Analog China, da ist die Länge der Vorwahl an der 1. Stelle erkennbar (1+2: 2-stellig, Rest 3-stellig). Nur - wenn man das international up-to-date halten will, hat man ARBEIT. Telefonnetze wachsen ständig, geraten ständig an irgendwelche Grenzen und werden darum ständig modifiziert.

Rolf

[Gunnar Bittersmann]

@@Rolf B

Dazu muss natürlich eine Liste der Landeskennzahlen vorliegen, damit der Algorithmus weiß, nach der wievielten Stelle die Landeskennzahl endet. Und für die einzelnen Länder (zumindest für einige) eine Liste der Ortsnetzkennzahlen.

Nur - wenn man das international up-to-date halten will, hat man ARBEIT.

Apple schafft das auf dem iPhone – das kann keine Raketenwissenschaft sein.

LLAP 🖖

[dedlfix]

Tach!

Apple schafft das auf dem iPhone – das kann keine Raketenwissenschaft sein.

Also so wie bei jeder Software: Schafft irgendwer, kann also nichts besonderes sein.

dedlfix.

[Rolf B]

Hallo Gunnar,

Ich sage ja nicht dass das ein Job für Mr. Musk ist, und sicherlich kann man das mittels eines Trie auch kompakter abbilden als ich in meiner SQL Tabelle. Aber wieviele Leute brauchen die dahinter, die auf Änderungen der nationalen Regelungen achten?

Rolf

[LastBoyScout]

Mit libphonenumber könnte man die Rufnummern natürlich konvertieren und bei Ausgabe wieder lesbar formatieren. Das Problem sind dabei nur die Durchwahlrufnummern, da diese damit in der Teilnehmerrufnummer aufgehen und es m.E. leider keine Regel gibt diese dann wieder abzuspalten:

• Eingabe: 089/1234-567
• Standardisiert: [phone]
• Formatiert: [phone] anstatt [phone]

Hier kann man es übrigens sehr schön testen.

[TS]

Hello,

schreib Dir eine Normalisierungsfunktion für das DBMS, die Du auf beide Seiten des Vergleichs anwendest.

Man könnte auch mittels Trigger oder eigenständigem Updatelauf die nicht normalisierten Einträge (die ich im übrigen auch erhalten würde) in eine zusätzliche normalisierte Spalte übertragen.

Die Normalisierung ausschließlich in die API (PHP-Modul) zu verlegen, halte ich für die schlechteste Möglichkeit, da man dann nie über andere APIs zugreifen dürfte auf die Datanbank.

Liebe Grüße
Tom S.

[TS]

Hello,

als kleine Ergänzung:
Google: Telefonnumern validieren

Es gibt dutzende von Vorschlägen. Du musst die Funktionen also nicht selber erfinden.

Liebe Grüße
Tom S.

[LastBoyScout]

Hi Tom,

als kleine Ergänzung:
Google: Telefonnumern validieren

Es gibt dutzende von Vorschlägen. Du musst die Funktionen also nicht selber erfinden.

Danke für den Hinweis zu libphonenumber... bleibt damit nur noch die Frage nach dem Format!?

[Rolf B]

Hallo LastBoyScout,

es ist häufig üblich, Telefonnummern in Datenbanken so zu speichern, dass ein Mensch ihnen ansehen kann was Ländervorwahl, Ortsnetz-Vorwahl, Ortsrufnummer und ggf. Anlagendurchwahl ist. Für eine Suche ist das völlig tödlich.

Ein Splitten dieser Nummernteile, so dass man sie getrennt speichern kann, macht die Sache nicht wirklich besser; es sei denn, du kannst die Eingabe in der Suche beeinflussen. Wenn der Suchstring einfach nur ein String ist, dann müsstest Du ihn in seine Bestandteile zerlegen um an Hand der Nummernteile in der DB suchen zu können.

Was Du keinesfalls tun solltest, ist ein SQL Statement zu bauen, dass während der Suche die Telefonnummer in der DB analysiert. Solche SQLs führen zu einem Table-Scan und sind extrem unperformant. Bei 100 Sätzen merkst Du es nicht, bei 1000000 Sätzen geht die DB in die Knie.

Meine Empfehlung wäre ein zweites Feld in der Datenbank, das indexiert ist. Das setzt natürlich voraus, das irgendwer dieses Feld pflegt. Entweder ein Batch-Job (CRON-Job), ein DB-Trigger oder die Datenpflege-Anwendung. Dieses Suchfeld enthält die Telefonnummer im internationalen Format, ohne jede Lesbarkeitshilfen. (0221)17940-100 würdest Du darin also als 4922117940100 speichern.

Aus Suchstrings entfernst Du vor der Suche die Lesbarkeitshilfen. Sodann musst Du wissen, ob deine Anwender alle deutsch sind, sonst kannst Du ihre Eingaben nicht normieren. Hast Du mehrere Eingabeländer, brauchst Du für jedes Land eine passende Eingabenormierung. Wenn jemand in Deutschland 022117940100 sucht, ist das der Kölner Dom. In der Schweiz landest Du damit irgendwo in Genf, in Österreich nirgends. Eine deutsche Eingabenormierung sieht so aus: Beginnt die Nummer mit + oder 00, ist sie international und du musst nur das + bzw. das 00 entfernen. Beginnt sie mit einer 0, ist sie national und du musst die 0 durch 49 ersetzen. Beginnst sie NICHT mit einer 0, ist es eine Ortsnetzrufnummer und du hast entweder Kontextinformationen, in welchem Ortsnetz der Anrufer sitzt, oder musst die Eingabe abweisen. Die Normierung für Österreich ist ähnlich, nur mit anderer Landesvorwahl. In der Schweiz gibt's gar keine Vorwahl. In Italien wird die führende 0 nicht mehr entfernt, und eine Nummer ohne führende 0 ist erlaubt (Mobilnummer). Und so weiter und so weiter. Je Land anders und jederzeit änderbar.

Mit der so normierten Nummer kannst Du dann im Telefonindex-Feld suchen.

Rolf

[Henry]

Hallo Rolf,

es ist häufig üblich, Telefonnummern in Datenbanken so zu speichern, dass ein Mensch ihnen ansehen kann was Ländervorwahl, Ortsnetz-Vorwahl, Ortsrufnummer und ggf. Anlagendurchwahl ist. Für eine Suche ist das völlig tödlich.

hm… interessant, habe damit noch keine schlechten Erfahrungen gemacht.

Was Du keinesfalls tun solltest, ist ein SQL Statement zu bauen, dass während der Suche die Telefonnummer in der DB analysiert. Solche SQLs führen zu einem Table-Scan und sind extrem unperformant. Bei 100 Sätzen merkst Du es nicht, bei 1000000 Sätzen geht die DB in die Knie.

nicht analysiert aber mit CONCAT zusammenfügt. Spricht da auch was dagegen?

Gruss
Henry

[Rolf B]

Hallo Henry,

ja, ein WHERE :bla = CONCAT(col1, col2, col3) dürfte eine Indexnutzung ebenfalls grillen, es sei denn, die genannten Spalten stehen in der Reihenfolge im Index und du hast einen schlauen Optimizer. Die Wahrheit wäre dann über EXPLAIN zu prüfen.

Rolf

[LastBoyScout]

Hallo Rolf,

Danke für deine ausführlichen Hinweise... merke schon die Sache ist wesentlich verzwickter als ich anfangs gehofft hatte.

Zum Glück handelt es sich lediglich um ein paar Hundert Datensätze welche zumindest alle gleich Formatiert sind (manche allerdings mit zusätzlicher Durchwahl). Beim Suchstring handelt es sich nicht um eine Formulareingabe, sonder die Anrufer-ID von janrufmonitor auch dieser ist daher immer gleiche Strukturiert.