Telefonnummer in Datenbank Finden

[ajmbarros]

Hallo,

ich möchte gerne eine Telefonnummer in einer Datenbank finden.
Kniffelig wird das ganze aber deswegen, weil es sich um ein VARCHAR(30) Feld handelt und über die Jahre sämtliche Zeichen die über Zahlen hinaus gehen verwendet wurden.

Ich habe mich gefragt ob es möglich ist das ganze mit Regulären ausdrücken zu bewältigen. Sprich: "is similar to".
Wie bereite ich aber den RegEx so vor, dass wenn z.B. die Telefonnummer "0123456" vorliegt ich auch "[phone]" finde aber auch "01234567" ausschließe?

Im Endeffekt brauch ich das für die Entwicklung einer TAPI Schnittstelle. Alle Telefonnummern Nachbearbeiten ist nicht möglich bzw. nicht gewünscht.

Gibt es dazu schon Lösungen, auch wenn es sich nicht um einen Regulären Ausdruck handelt?
Ich habe schon lange danach gesucht aber nichts vernünftiges gefunden. Auch den Ausdruck konnte ich nicht selbst bauen.

Bin für alle Vorschläge dankbar!

[himitsu]

Antwort:
* Wert auslesen
* alle Zusatzzeichen entfernen
* Vorwahl auflösen/einfügen
* das jeweils bei beiden Werten
* und danach kann man Beides problemlos vergleichen

Um schneller zu suchen, könntest du eine zusätzliche Spalte anlegen, welche z.B. per Trigger gefüllt/abgeglichen wird und wo die Daten der Tabelle bereits umgewandelt rumliegen.
Da geht dann das Suchen schneller und noch schneller mit Index darauf.

Oder halt eine eigene Vergleichsfunktion erstellen, welche zwei Werte bekommt, diese wie oben umformt und am Ende das Vergleichsergebnis zurückliefert.

[Der schöne Günther]

Ich verstehe nicht. FireBird unterstützt doch mit SIMILAR TO reguläre Ausdrücke oder interpretiere ich das falsch?
http://www.firebirdsql.org/refdocs/l...ilding-regexps



Rofl:

Zitat:

: The following syntax defines the SQL regular expression format. It is a complete and correct top-down definition. It is also highly formal, rather long and probably perfectly fit to discourage everybody who hasn't already some experience with regular expessions (or with highly formal, rather long top-down definitions). Feel free to skip it and read the next section, Building regular expressions, which uses a bottom-up approach, aimed at the rest of us.

[ajmbarros]

Reguläre ausdrücke werden unterstützt, aber:

Code:

select *
from Kontakte k
where k.tel is similar to '?'

Ich habe 0123456 als ausganswert.
Wenn ich genau die Telefonnumer suche entgehen mir die Nummern mit Sonderzeichen o.ä.

Es müsste, nach meinen Idealvorstellungen so sein (geht aber nicht):

Code:

select *
from Kontakte k
where regex(k.tel,'/d{1,}' = '0123456'

wobei "/d{1,}" für eine beliebige anzahl von Zahlen steht.

Oder es müsste eine Art von "Cast" funktionieren, aber auch das fand ich nicht.

[himitsu]

Was gibt denn diese regex-Funktion für ein Ergebnis raus? :zwinker:

Entweder alle Zahlen-Funde, welche aus dem regex rauskommen, wieder zu einem String zusammensetzen

oder andersdrum könnte es auch gehn -> via Regex-Replace alles Ungültige entfernen


Aber, wie erwähnt, mußt du bei den Vorwahlen aufpassen

[phone]
[phone]
0351 666666
0049 351 666666
usw.

Entweder die verschiedenen Landesvorwahlen entfernen (wenn nur in Deuschland) oder die Vorwahlen vereinheitlichen.

[ajmbarros]

Genau das ist mein Problem. Diese Funktion gibt es meines Wissens nicht. Die erste Variante existiert. Allerdings weiß ich nicht wie der regulärer Ausdruck gebaut werden könnte.

[Sir Rufo]

Man sollte eben immer darauf aufpassen, wie die Daten in der Datenbank abgelegt werden. So wie es scheint ist die Telefonnummer stumpf als Text dort gespeichert worden, was sich nun rächt. Besser wäre es gewesen und ist es, solche Daten kontrolliert in die Datenbank zu schreiben.

Es ist unerheblich, in welcher Art diese in der Datenbank abgelegt werden, es muss nur eben reproduzierbar sein. Helfen kann dabei ein sogenanntes ValueObject (in Delphi am einfachsten mit einem Record darstellbar).

Dieses ValueObject wird dann mit einem Konstruktor erzeugt und der lässt eben nur gültige Einträge zu. Gerade bei Rufnummern kann man da auch ein sehr schönes Konstrukt bauen, was dann abhängig vom aktuellen Kontext die Rufnummern ausgibt und auch annimmt.

Nehmen wir an wir befinden uns in Hamburg, dann ist der Kontext +49 40. Erzeugt man nun ein VO mit 123456 dann wird das intern und für die Datenbank als [phone] gespeichert. Bei einer Abfrage würde aber wieder nur 123456 herauskommen.

Die gleiche Rufnummer ergibt für den Kontext Berlin +49 30 dann 040 123456.

Im Übrigen gibt es hier im Forum ein bisserl Code von mir genau zu dem Thema Rufnummer Validierung und Formatierung. Einfach mal die Suche bemühen.

[Dejan Vu]

Ergänzung:
Sind das meine Daten, d.h. hat meine Anwendung exklusiven Zugang zu den Daten? Dann gerne bei der Erfassung normalisieren.
Sind das Altlasten bzw. Fremddaten, dann lieber den Vorschlag von himitsu verwenden.

Mit RegEx bei der Suche würde ich hier nicht arbeiten, denn es gibt dann immer Fälle, die unter den Tisch fallen. Bei den beiden anderen Varianten kann ich das aber merken bzw. dann tritt das gar nicht auf (VO)

[jobo]

Also eine Normalisierung der gespeicherten Fassung der Nummer und eine identisch normalisierte gesuchte Nummer sollte doch in jedem Fall der richtige Ansatz sein.
Selbst wenn schreckliche Altdaten vorliegen, sollte versuchen, diese Schmerzen irgendwann loszuwerden, also Altdaten konvertieren/normalisieren und Neueingaben dann analog.
Wenn die Prio die Suchgenauigkeit ist und nicht so sehr die Performance und man nicht ohne weiteres das Speicherformat umstellen kann, könnte man auch eine Normalisierungsfunktion on the fly nutzen.
Das taugt sehr wahrscheinlich nicht für größere Datenmengen.

[ajmbarros]

Ok, es gibt eben nicht genug Funktionen in Firebird.
Ich werde das Ganze auf Grund der Vorschläge so machen:

1) neues Feld, über extra dafür geschriebenes Tool alle Numern "sauber" in neuen Feld abspeichern.
2) bei Eingabe zweites Feld sauber füllen.

In meinem Fall bietet sich sogar dafür eine neue Tabelle an da die Nummern aus zwei Tabellen kommen.
Ich kann's halt nicht zur Laufzeit mit allen Nummern machen. Ich hab nicht mehr als eine Sekunde zeit aus über 3 Millionen Nummern die richtige rauszusuchen.
Ganz löschen kann ich den Kram (erstmal) auch nicht da das Feld auch noch zweckentfremdet wurde und teilweise wichtige Kurzinformationen drin stehen die nicht verloren gehen dürfen, aber auch keine Telefonnummer sind.

Saubermachen kann halt manchmal nicht umgangen werden. Auch nicht wenn's ne Altlast eines anderen ist.

Danke an alle!

[ajmbarros]

Zumachen nicht vergessen...

[jensw_2000]

Zitat:

Zitat von ajmbarros (Beitrag 1282206)

Ok, es gibt eben nicht genug Funktionen in Firebird.

Man kann sich mit Delphi sehr einfach Firebird UDFs (userdefined functions) schreiben und den Funktionsumfang damit problemlos erweitern.
Hier mal ein Link dazu.
Firebird UDF mit Delphi ...

Der alte Firebird hatte damals kaum Date/Time Funktionen im Angebot. Dafür hatte ich für einen Kunden die halben Delphi DateUtils per DLL als UDF bereitgestellt.

Du kannst Dir also einfach eine Firebird "Addin" DLL in Delphi schreiben, die Dir Folgendes ermöglicht:

Code:

SELECT 
  * 
FROM 
  KONTAKTE K 
WHERE
  NormalizedPhoneNumber(K.Telefon) = '04040404040404040';

"NormalizedPhoneNumber" ist eine Delphi Funktion, die per DLL bereitgestellt wird und etwa so aussehen könnte:

Delphi-Quellcode:

function NormalizedPhoneNumber(const aTelefonnummer:WideString):WideString;
  var t:String;
begin
  t := StringReplace(aTelefonnummer,'+','00');
  t := RemoveNoneNumericCharsFromString(t);

  result := t;
end;

[himitsu]

Die implizite "(0)" nicht vergessen zu entfernen,
wenn keine Landesvorwahl vorhanden, dann diese hinzufügen
usw.

[ajmbarros]

Geht das auch für Linux?

[jensw_2000]

Kann ich nicht sagen. Hatte bisher nur Windows Firebird Server in den Fingern.

[dataspider]

In Firebird zumindest seit der 2.x ' er Version kann man die meisten UDF' s durch SP' s ersetzen.

Code:

create or alter procedure nur_ziffern (
    wert varchar(80)
returns (
    result varchar(80))
as
declare variable laenge smallint$;
declare variable pos smallint$;
declare variable zeichen char1$;
begin
  laenge = char_length(:wert);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:wert from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9')) then
        result = coalesce(:result, '')||:zeichen;
      pos = :pos + 1;
    end 
  end
  suspend;
end

Ich führe immer ein Feld mit (Bsp. nummer$, welches nur die Ziffern enthält.
Dieses wird über einen Trigger aktuell gehalten:

Code:

CREATE OR ALTER TRIGGER TELEFON_BUI1 FOR TELEFON
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
as
begin
  execute procedure nur_ziffern(new.nummer) returning_values new.nummer$;
end

Das Feld ist indexiert und wird für die Suche benutzt.

Frank

[ajmbarros]

Das sieht sehr gut aus. Werde ich gleich mal in die Tat umsetzten.

[ajmbarros]

Anbei die Funktionierende Lösung dank einer Procedure:

Code:

CREATE PROCEDURE STRTOINT(
  STRINGWERT VARCHAR(80) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
RETURNS(
  RESULT VARCHAR(80) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)
AS
DECLARE VARIABLE laenge INTEGER;
DECLARE VARIABLE pos INTEGER;
DECLARE VARIABLE zeichen CHAR(1);
BEGIN
  /* Procedure body */
  
  laenge = char_length(:STRINGWERT);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:STRINGWERT from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9')) then
        result = coalesce(:result, '')||:zeichen;
      pos = :pos + 1;
    end
  end
  
  SUSPEND;
END;

Und mit diesem select ist auch kein zweites Feld nötig:

Code:

select *
from ANSPR a
where (select result from  STRTOINT(a.TEL)) = '0123456'

Ich hab natürlich einige Datenbank-Reads aber es hält sich in unter einer Sekunde.

Evtl. muss ich mit den Landesvorwahlen tricksen bzw. muss ich mal testen wie gut das mit einem like '%0123456' funktioniert, wenn ich vorher im Delphi-Code die Landesvorwahlen immer raus nehme. Nochmals vielen Dank!

[Dejan Vu]

Zitat:

Zitat von ajmbarros (Beitrag 1282313)

Und mit diesem select ist auch kein zweites Feld nötig:

Muss ja nicht, aber wenn Du mal mehr Daten hast, würdest Du dich freuen. 'Unter einer Sekunde' ist übrigens bei den paar Daten ein GAU, imho. Eine DB sollte zum Finden einzelner Sätze 0.1 Sekunden brauchen, mehr nicht. Ein Table-Scan sollte bei Standardsuchen vermieden werden, finde ich.

[p80286]

Was soll's, er hat verkorkste Daten, eine Möglichkeit so zu tun als sei alles in Ordnung, also geht's weiter so.

http://de.wikiquote.org/wiki/Max_Liebermann

Gruß
K-H