Zeichen bei SELECT ignorieren

[fillibuster]

Hallo,

ist es möglich mit SQL eine Abfrage über String-Felder zu machen und dabei bestimmte Zeichen zu ignorieren bzw. nur nach Zahlen zu suchen? Konkret geht es mir darum in Telefonnummern zu suchen. Die Nummer inkl. Vorwahl bekomme ich ohne führende Null als reine Zahlenzeichenkette. In der DB sind aber auch Zeichen (+/...) abgespeichert.

Danke!

[Sharky]

Hai fillibuster,

ich würde nicht versuchen zeichen zu ignorieren sondern die Daten in der Tabelle in eine vernünftige Form zu bringen.

Also drei Felder für eine Telefonnumemer

LKZ - Landeskennzahl ohne nullen oder so (z.B. 49 für Deutschland, 1 für USA/Nordamerika, 44 für England)
OKZ - Ortskennzahl auch ohne nullen (z.B. 89 für München, 69 für Frankfurt)
Nummer - Die Nummer des Teilnehmers.

Dann kannst Du vernünftig suchen.

Anstelle der LKZ würde es sich auch anbieten eine Tabelle mit allen LKZ anzulegen und bei dem "Kontakt" dan nur die ID der LKZ aus der Tabelle zu verwenden.

[p80286]

@Sharky
Da träumt aber manche "prof. Personen Daten Verwaltung" von.

@fillibuster
eine andere Möglichkeit wäre es die Tel.Nummern gleich richtig zu speichern:
[phone]

Gruß
K-H

[fillibuster]

Hallo,

die Tabelle jetzt noch umzuformen ist nicht möglich. Die einzige Möglichkeit, die in der Richtung funktionieren würde, wäre ein zusätzliches Feld und alle Nummern bereinigt eintragen. Das ist selbst aber für den konkreten Fall viel zu umständlich, deshalb: gibt es eine Möglichkeit mit SQL Nummern zu suchen und dabei alle anderen Zeichen zu ignorieren?

Viele Grüße ...

BTW: Ich glaube beim Speichern von Telefonnumern gibt es min. 25 Ansichten, wie es richtig geht ;-)

[joachimd]

Erstelle Dir in SQL eine Function, welche ein vergleichbares Format hervorbringt und nutze diese.

[p80286]

Zitat:

Zitat von fillibuster (Beitrag 1233656)

Hallo,

die Tabelle jetzt noch umzuformen ist nicht möglich. Die einzige Möglichkeit, die in der Richtung funktionieren würde, wäre ein zusätzliches Feld und alle Nummern bereinigt eintragen. Das ist selbst aber für den konkreten Fall viel zu umständlich, deshalb: gibt es eine Möglichkeit mit SQL Nummern zu suchen und dabei alle anderen Zeichen zu ignorieren?

Dann schau mal nach was Dein SQL-Dialekt her gibt. Je nachdem werden z.B. solche Konstrukte unterstützt:

Code:

select irgendwas
from meinetabelle
where satz like '['+','-']['0'..'9']__%'

das könnte weiter helfen.

Zitat:

Zitat von fillibuster (Beitrag 1233656)

BTW: Ich glaube beim Speichern von Telefonnumern gibt es min. 25 Ansichten, wie es richtig geht ;-)

dann schau mal hier herein.

Gruß
K-H

[baumina]

Ich bereite mir den Suchstring für die Telefonnummer, die von der Telefonanlage kommt folgendermaßen auf, um sie dann in meiner Datenbank zu suchen:

Delphi-Quellcode:

  for i := 1 to Length(TelNr) do
  begin
    SuchTel := SuchTel + '%' + TelNr[i];
  end;
  SuchTel := SuchTel + '%';


  lSQL.Add('WHERE Telefon1 like ' + StrToSQL(SuchTel));
  lSQL.Add('   OR Telefon2 like ' + StrToSQL(SuchTel));
  lSQL.Add('   OR MobilTel like ' + StrToSQL(SuchTel));

[Furtbichler]

Das bringt doch nichts, wenn die gespeicherte Nummer z.B. '01 23 45' lautet und Du nach '012345' suchst.

Da SQLLite verwendet wird, scheint die Datenmenge nicht sonderlich groß zu sein. Also alles in den Speicher, dann suchen und fettig.

Delphi-Quellcode:

Query.Text := 'Select ID,Telefonnummer from Tabelle';
Query.Open();
While not Query.EOF do begin
  if PhoneNumberMatches(MySearchString, Query['Telefonnummer']) Then
    Results := Results + Query['ID']+',';
  Query.Next;
End;
if Results<>'' then begin
  setlength (Results, Length (Results) - 1);
  Query.Close;
  Query.Text := 'Select * from Tabelle where ID in ('+Results+')';
  Query.Open;

Sind halt 2 Queries. Die erste Query liefert alle Telefonnummern und die Record-ID. Dann wird eine Liste von IDs der Records erzeugt, deren Telefonnummer past und anschließend werden nur diese Records gelesen.

Mit einem anderen RDBMS kann man sich eine mehr oder minder komplexe Funktion (UDF) erstellen, das Matchen übernimmt, aber mit SQLite geht das eben nicht.

[DeddyH]

Das stimmt so nicht ganz, aber der Aufwand dürfte abschrecken (Punkt 2.3).

[Jumpy]

Nicht schön aber selten, könnte es vllt. nur in SQL auch so gehen:

SELECT * FROM Tabelle
WHERE replace(replace(replace(replace(Telefonnumer,'+',' '),'-',''),'/',''),' ','') LIKE '%12345%'

Das ist jetzt wie man das in Oracle machen könnte. Laut der Doku im Link von DeddyH kennt SQL zumindest eine replace Funktion. Keine Ahnung was man bei SQL Light für Platzhalterzeichen statt % verwendet.

[Furtbichler]

Zitat:

Zitat von DeddyH (Beitrag 1233743)

Das stimmt so nicht ganz, aber der Aufwand dürfte abschrecken (Punkt 2.3).

:thumb: Nett. Aber ne SQL-UDF ist das tropsdem nich.

[p80286]

Ich hatte vor einiger Zeit ebenfalls das Vergnügen mich mit Telefonnummern herum zu schlagen.
Nach einer Woche Hampelei mit Berücksichtigung von deutschen,amerikanischen,englischen und Sekretärinenvorlieben (alles natürlich wohlbegründet und jede Änderung ein Verstoß gegen die Menschenrechte) hat ein netter Mensch mit der Faust auf den Tisch gehauen, und ein Standardformat definiert. Die Umstellung war nach 2 Stunden (mit Delphi) erledigt.
Jetzt läuft jede Woche eine Prüfabfrage die eine Fehlerliste generiert und gut ist.

Gruß
K-H

[fillibuster]

Hi,

erstmal danke für eure Antworten. Da teste ich mir das Passende raus. Hatte gestern festgestellt, dass die folgende Abfrage auch funktioniert:

Code:

SELECT * FROM contact WHERE phone LIKE '%3%0%1%2%3%4%5%6'

für z. B. [phone]. Ist ja bald Halloween :shock: *grusel*

Viele Grüße ...

[baumina]

Zitat:

Zitat von fillibuster (Beitrag 1233811)

Hi,

erstmal danke für eure Antworten. Da teste ich mir das Passende raus. Hatte gestern festgestellt, dass die folgende Abfrage auch funktioniert:

Code:

SELECT * FROM contact WHERE phone LIKE '%3%0%1%2%3%4%5%6'

für z. B. [phone]. Ist ja bald Halloween :shock: *grusel*

Viele Grüße ...

Das entspricht dann meiner Lösung, nur gebe ich hinter der 6 auch noch ein % mit.

[fillibuster]

Hi,

Zitat:

Zitat von baumina (Beitrag 1233814)

Das entspricht dann meiner Lösung, nur gebe ich hinter der 6 auch noch ein % mit.

Stimmt, hatte aber erst gar nicht gedacht, dass so ein SQL Konstrukt funktioniert :stupid:

Danke euch :thumb:

[Furtbichler]

Zitat:

Zitat von fillibuster (Beitrag 1233811)

Code:

SELECT * FROM contact WHERE phone LIKE '%3%0%1%2%3%4%5%6'

für z. B. [phone].

Und [phone] passt auch :thumb:

[baumina]

Zitat:

Zitat von Furtbichler (Beitrag 1233867)

Zitat:

Zitat von fillibuster (Beitrag 1233811)

Code:

SELECT * FROM contact WHERE phone LIKE '%3%0%1%2%3%4%5%6'

für z. B. [phone].

Und [phone] passt auch :thumb:

Aber die Ergebnismenge bleibt überschaubar und kann zur Not nochmal genauer betrachtet werden.

[Furtbichler]

Zitat:

Zitat von baumina (Beitrag 1233875)

Aber die Ergebnismenge bleibt überschaubar und kann zur Not nochmal genauer betrachtet werden.

Das muß nicht 'zur Not' genauer betrachtet werden, sondern in jedem Fall. Und wenn Du dich darauf verlässt...nun ja. Wenn die Stecknadel in einer Handvoll Heu steckt, ist sie immer noch schwer zu finden.

Ich stehe auf dem Standpunkt, das ein Problem, das ordentlich lösbar ist, auch ordentlich gelöst werden sollte.

PS: Als Lösung für die Eingabe kompletter Telefonnummern mag es als Notbehelf durchgehen, ich würde die paar Daten (da sie ja überschaubar sind) trotzdem in-Memory filtern, und dann richtig.

[baumina]

Ich habe in meiner Datenbank das Problem, dass ein und die selbe Telefonnummer mehr als einmal in der Datenbank drin stehen kann.

z.B.
1. Hans Müller als Kunde mit Telefonnummer
2. Hans Müller als Ansprechpartner seiner Firma mit Privatnummer
3. Hans Müller als Ansprechpartner seiner 2. Firma mit Privatnummer
usw....

Sollte nun unser Telefon klingeln, und Hans Müller ruft von seinem Privattelefon an, wird's spannend :-)

[Furtbichler]

Namen sind nicht eindeutig, Telefonnummern schon (eher).

Ich sagt bereits, das dein Vorschlag als Notbehelft zu gebrauchen ist. Aber nur, wenn die Telefonnummern geeignet abgelegt ist. Und was ist morgen?

Konkret: Dein Vorschlag wird umgesetzt und alle sind zufrieden, denn er funktioniert für die aktuelle Problemstellung: Suche nach vollständiger(!) Telefonummer über die derzeit gespeicherten Nummern (die z.B. nach E.123 abgelegt sind).

Morgen werden jedoch die Outlook-Kontakte importiert und dort wird die Telefonnummer nach dem MS-Standard abgelegt, also [phone] (in der DB sind sie jedoch im Format [phone] abgelegt. Ja und dann ruft Hans Müller an, seine Nummer 03012345678 erscheint im Display und deine Suche findet -nichts-. Der Sachbearbeiter kann den Kunden nicht zuordnen und legt ihn im schlimmsten Fall neu an.

Irgendwann wird dann vergessen, das die Lösung erstens nicht immer das korrekte Ergebnis anzeigt, zweitens nur taugt, wenn man die zu suchende Telefonnummer vollständig angibt und drittens doch nur mit einem bestimme Format funktioniert, also eben z.B. nur E.123, aber nicht mit anderen Standards. Und spätestens dann ist die Suche keine Suche sondern ein Glücksspiel.

Deine Lösung zeigt also nicht nur zu viele an (was zu verschmerzen wäre), sondern ist leider unzuverlässig. Und damit imho so nicht zu gebrauchen.

PS: Mein Beispiel funktioniert auch, wenn die Nummern nur im MS-Format abgelegt sind, also mit internationaler Vorwahl, jedoch ohne führende Null in der Ortsvorwahl. Denn es gibt generell mindestens vier unterschiedliche Formatgruppen, die hinsichtlich der einfache Suche disjunkt sind:
Ortsvorwahl: (30) vs. (030).
Landeskennzahl: +49 vs. 0049 vs. ganz weglassen.

Wie Du siehst, wird es nie zuverlässig funktionieren, so elegant und kurz dein Vorschlag auch ist.

Ein Suchalgorithmus muss robust gegen alle Kombinationen sein, d.h. er muss die gespeicherten und die Such-Telefonnummer in LKZ, OnKZ und Rufnummer unterteilen und dann vergleichen.

Richtig richtig macht man es imho nur dann, wenn die Telefonnummern normalisiert abgelegt werden. Alles andere ist Murks.

[baumina]

Ja, mit diesem einheitlichen Format habe ich mich lange und ausgiebig auseinandergesetzt. Sicherlich bin ich nicht die einzige, die solch eine Konstellation hat:

1. Firmeneigene Adressdatenbank
2. Exchange-Kontakte
3. Outlook auf PCs
4. SmartPhones mit verschiedenen Betriebssystemen

Meine Adressdatenbank füllt die Exchange-Datenbank, die Exchange-Kontakte werden mit den Outlooks und den SmartPhones synchronisiert.

Und nun die Sache mit dem Telefonnummern-Format. Microsoft war so frei sich ein eigenes Telefonnummern-Format zu erfinden, mit dem Exchange und Outlook problemlos arbeitet. Munter gibt man nun alle Telefonnummern in diesem MS-spezial-Format (siehe http://de.wikipedia.org/wiki/Rufnummer) ein. Und dann passiert es ... ich kann mich nicht mehr genau erinnern welches Smartphone mit welchem Betriebssystem es war, auf jeden Fall konnte man mit diesem Smartphone keine einzige Nummer aus dem Exchange-Telefonbuch anrufen, weil es mit dieser Formatierung nicht zurecht kam.

Aus diesem Grunde haben wir und dazu entschlossen dieses MS-Format nicht zu benutzen.

[jobo]

MS ist soweit mir bekannt ein großer Sponsor von Wikipedia.
Was das bedeutet, kann sich jeder selber überlegen.

[baumina]

Zitat:

Zitat von jobo (Beitrag 1233888)

MS ist soweit mir bekannt ein großer Sponsor von Wikipedia.
Was das bedeutet, kann sich jeder selber überlegen.

Dann würde Wikipedia das MS-Format als super gut hinstellen, oder die anderen genormten Formate erst gar nicht erwähnen ... ist aber nicht so.

[jobo]

Mmh, wenn das so da stand, wie es nun da steht, dann habt Ihr Euch ja mit MS scheinbar freiwillig gegen ein sinnvolles Format entschieden oder?
Ich habe den Link ehrlich gesagt nicht gelesen, bevor ich meinen Beitrag geschrieben habe. Auch wenn der Artikel offenbar recht ausgewogen ist, kann der Hinweis auf den großen Spender sicher nicht schaden.

[baumina]

Ja genau, wir haben uns dazu entschieden, dass unsere Adressverwaltung mit allen Telefonnummernformaten klar kommt. So werden Telefonnummern aus der DB so an eine Telefonanlage übergeben, dass diese die Nummer wählen kann und eingehende Anrufe egal in welchem Format die Nummer in der DB liegt, in der DB gefunden und angezeigt werden kann.

[Furtbichler]

Zitat:

Zitat von baumina (Beitrag 1233886)

Aus diesem Grunde haben wir und dazu entschlossen dieses MS-Format nicht zu benutzen.

Es kommt auch ohne MS zu Inkonsistenzen, nämlich genau dann, wenn man sich nicht auf ein Format festlegt und alle Importer und Eingaben entsprechend anpasst. Ideal wäre die Trennung von LKZ, OnKZ und Rufnummer. So haben wir das gemacht. Bei der Adresseingabe wird dann zum Land bzw. PLZ/ZIP gleich die LKZ und OnKZ vorgeschlagen.

[p80286]

Wir nutzen das MS-Format, da es dem "Ordnungssinn" des/der Datenerfassenden nahe kommt.
Insbesonders bei proportionalen Fonts ist die Darstellung von Leerzeichen so katastrophal, das man mit diesem Format gut bedient ist.
Und vom einen in das andere Format zu wechseln ist ja relativ einfach (ok, die Durchwahl wird nicht abgetrennt, aber die muß man nur dann kennen wenn man von der einen Nummer auf die andere schließen will, da kan man auch gleich erfasssen)

Zitat:

Richtig richtig macht man es imho nur dann, wenn die Telefonnummern normalisiert abgelegt werden. Alles andere ist Murks.

:thumb::thumb:
Gruß
K-H

[baumina]

Ja eine Trennung in Ländervorwahl, Ortsvorwahl, Rufnummer und Durchwahl wäre traumhaft, nur wie bekomme ich meine aktuellen Daten in dieses Format ohne das manuell pflegen zu müssen.

[joachimd]

Zitat:

Zitat von baumina (Beitrag 1233974)

Ja eine Trennung in Ländervorwahl, Ortsvorwahl, Rufnummer und Durchwahl wäre traumhaft, nur wie bekomme ich meine aktuellen Daten in dieses Format ohne das manuell pflegen zu müssen.

Du läufst jede Nummer von vorne durch ... ist es eine Ländervorwahl, abtrennen und mit dem Rest weitermachen. Fehlt die Ländervorwahl, eine Default (Deutschland oder abhängig von der Länderspalte im Datensatz) vergeben. Dann mit der Vorwahl weitermachen und die von der Rufnummer trennen (Vorwahl-Listen gibt es zuhauf im Internet zu finden). Was bleibt, ist die Rufnummer (evtl bereinigen wg Leerzeichen, Durchwahl-Trennern usw).