Telefonnummern trennen in Vorwahl-Rufnummer mit TSQL

[jensw_2000]

Hallo Leute,

ich brauche mal einen kleinen Denkanstoß.

Beim Import von Kundendaten in meine Callcenter Software muss die Telefonnummer in folgendem Format vorliegen, damit sie für einige Programmfunktionen bzw den späteren Export sauber getrennt werden kann:

Code:

Ortsnetzkennzahl-Rufnummer

Es kommt immer wieder vor, das eine Importdatei kein Trennzeichen (Leerzeichen, Bindestrich o.Ä.) zwichnen der Vorwahl und der Telefonnummer hat.
Meine Kunden sind dann so kreativ, das sie die Importdatei mit Excel bearbeiten und bei den Telefonnummern einfach pauschal nach der 4. Ziffer ein "-" einfügen.

Bisher war das kein Problem.
Jetzt bin ich dabei ein Plugin zum Verkauf von "?-"DSL zu entwicken. (Besser gesagt, Montag ist Abgabetermin ... :shock: )
Für den Verfügbarkeitscheck werden Vorwahl und Telefonnummer als Eingabeparameter erwartet.

Damit der Kunde keine Möglichkeit mehr hat, die Telefonnummern nach Phantasie zu trennen möchte ich eine UDF basteln, die alle Sonder und Leerzeichen aus der Telefonnummer entfernt.
Dann soll die Rufnummer mit einer bestehenden Vorwahlenliste abgeglichen werden.
Die Funktion soll die fertig formattierte Rufnummer ("Vorwahl-Rufnummer") aus Ausgabeparameter zurückliefern.


Sicher könnte man die Telefonnummer nach und nach am Ende abschneiden und dann dann prüfen ob sie in der Vorwahlentabelle existiert. Das ist aber deutlich zu langsam.

Derzeit finde ich aber keinen Ansatz für eine bessere und schnelle Lösung ... :roll:

Habt ihr dazu eine gute Idee ?




Schöne Grüße,
Jens
:hi:

[sakura]

Gib dem Thread bitte einen vernünftigen Titel ;)

Danke,
...:cat:...

[jensw_2000]

Zitat:

Zitat von sakura

Gib dem Thread bitte einen vernünftigen Titel ;)

Danke,
...:cat:...

Fürchterlich, das beim einfügen eines Entwurfs immer der Titel überschrieben wird...
:roll: :mrgreen:

[Duffy]

Hallo Jens,
warum trennst Du nicht schon bei der Eingabe? Klassisch gesehen zwei Eingabefelder für Vorwahl und Telefonnummer.

By
Claus

[SirThornberry]

warum willst du irgendwas abschneiden? du hast doch alle vorwahlen jetzt sicher in einer Liste. Frag also einfach ab welche Telefonnummer (vorwahl + Telnummer) mit TestVorwahl anfängt und bei den gefunden musst du ja dann einfach von vorwhl + Telnummer am anfang die Länge von TestVorwahl abschneiden um die eigentliche Telefonnummer zu bekommen. Sollte auch nicht all zu lange dauern.

Ändere bitte auch den Betreff, der entspricht nicht den Regeln der DP.

Durch lesen der anderen Beiträge kam jetzt eine Frage bei mir auf. Sind derzeit in der DP Vorwahl und eigentliche Tel-Nummer in der DB nicht getrennt? Wenn dem so ist, hast du vor das weiter so zu belassen?

[jensw_2000]

Zitat:

Zitat von Duffy

Hallo Jens,
warum trennst Du nicht schon bei der Eingabe? Klassisch gesehen zwei Eingabefelder für Vorwahl und Telefonnummer.

By
Claus

Das wäre der Optimalfall.

Leider unpraktikabel, weil die Callcenter ihre Kundendaten idR von großen Adresslieferanten kaufen oder weil sie die Daten von einem Auftraggeber vorsetzt bekommen.

Das Format der Rohdaten ist dabei sehr individuell.
Meist ist keine Trennung von Vorwahl und Rufnummer vorhanden.

[SirThornberry]

wäre es nicht das effektivste wenn du jetzt so eine vorwahlliste hast das du beim importieren der Telefonnummern dort schon die Vorwahl ermittelst und dann die daten in 2 Felder der Datenbank einträgst?

[Duffy]

Hallo Jens,

SirThornberry war schneller …

dann musst Du die Telefonnummer normalisieren in dem Du alles was nicht numerisch ist entfernst. Jetzt brauchst Du nur noch einen Check auf die Vorwahlen machen, in dem Du eine Vorwahlenliste (diese ist dann Pflicht) dazu benutzt, um eine Vorwahl zu klassifizieren. Wenn Du einen Treffer hast, ist es klar, der Rest der Telefonnummer ist die eigentliche Nummer.

bye
Claus

[marabu]

Hallo Jens,

bei einem binary search mit der normalisierten Rufnummer in der ONKZ-Tabelle wird (a) nie ein Treffer erfolgen und (b) die passende Vorwahl immer der Eintrag vor der insert position sein.

Grüße vom marabu

[SirThornberry]

marabu scheint die gleiche idee gehabt zu haben wie ich, hab nämlich grad mal sowas zum test geschrieben wie man die Vorwahl ermittelt.

Delphi-Quellcode:

var LVorwahlen: TStringList;
    LIndex: Integer;
begin
  LVorwahlen := TStringList.Create;
  LVorwahlen.Sorted := True;
  //LVorwahlen mit Vorwahlen aus DB füllen

  if LVorwahlen.Find(Edit1.Text, LIndex) then
    ShowMessage('es handlet sich um eine Vorwahl.')
  else begin
    if (LIndex > 0) and AnsiStartsText(LVorwahlen.Strings[LIndex-1], Edit1.Text) then
      ShowMessage('Vorwahl ist: ' + LVorwahlen.Strings[LIndex-1])
    else
      ShowMessage('Keine Vorwahl gefunden');
  end;

  LVorwahlen.Free;

[jensw_2000]

Zitat:

Zitat von SirThornberry

warum willst du irgendwas abschneiden? du hast doch alle vorwahlen jetzt sicher in einer Liste. Frag also einfach ab welche Telefonnummer (vorwahl + Telnummer) mit TestVorwahl anfängt und bei den gefunden musst du ja dann einfach von vorwhl + Telnummer am anfang die Länge von TestVorwahl abschneiden um die eigentliche Telefonnummer zu bekommen. Sollte auch nicht all zu lange dauern.

Ändere bitte auch den Betreff, der entspricht nicht den Regeln der DP.

Durch lesen der anderen Beiträge kam jetzt eine Frage bei mir auf. Sind derzeit in der DP Vorwahl und eigentliche Tel-Nummer in der DB nicht getrennt? Wenn dem so ist, hast du vor das weiter so zu belassen?

Hier mal mein erster Ansatz ...

SQL-Code:

CREATE FUNCTION SYSF_GetSplittedTelNumber (@TelNr VARCHAR(25) )
RETURNS VARCHAR(25)
AS
BEGIN
  DECLARE @TestVorwahl VARCHAR(25) 
  DECLARE @Dummy VARCHAR(25)
  DECLARE @TempResult VARCHAR(25)
  
  SET @TestVorwahl = @TelNr
  
  NeueSuche:
    SELECT @Dummy=Vorwahl
    FROM SYSF_Vorwahlen()
    WHERE Vorwahl= @Testvorwahl

    IF @@ROWCOUNT=0 AND LEN(@TestVorwahl)>0 
    BEGIN
      SET @TestVorwahl=LEFT(@TestVorwahl,Len(@TestVorwahl)-1)
      GOTO NeueSuche
    END

  SET @TempResult= @TestVorwahl+'-'+RIGHT(@TelNr,LEN(@TelNr)-LEN(@TestVorwahl))

  IF LEFT(@TempResult,1)='-' SET @TempResult=RIGHT(@TempResult,LEN(@TempResult)-1)

  RETURN @TempResult

  
END

Dumm ist das ich hiermit ...

SQL-Code:

  NeueSuche:
    SELECT @Dummy=Vorwahl
    FROM SYSF_Vorwahlen()
    WHERE Vorwahl= @Testvorwahl

    IF @@ROWCOUNT=0 AND LEN(@TestVorwahl)>0 
    BEGIN
      SET @TestVorwahl=LEFT(@TestVorwahl,Len(@TestVorwahl)-1)
      GOTO NeueSuche
    END

... im Extremfall 15 TableScans in der Vorwahlentabelle mache ...

Die Funktion läuft im Moment ca. 0,5 Sekunden bei 100% CPU Costs ...

Die Rufnummern werden getrennt gespeichert.
Um die Rufnummer, egal wie sie kommt, trennen zu können, brauche ich die oben beschriebene Funktion ...

[jensw_2000]

@SirThornberry

Für Delphi wäre das eine brauchbare Lösung, aber ich importiere die Daten über T-SQL direkt auf dem SQL-Server.
Das Programm schupst nur eine Stored Procedure in der DB an und die Daten sind in der Kundentabelle ...


Schöne Grüße,
Jens
:hi:

[marabu]

Hallo Jens,

in T-SQL musst du die ONKZ-Tabelle mit einem less-than Vergleich an deiner normierten Nummer zerlegen, die Ordnung des Zwischenergebnisses umkehren und mit TOP 1 greifst du dann auf die korrekte Vorwahl zu.

Grüße vom marabu

[jensw_2000]

Zitat:

Zitat von marabu

Hallo Jens,

in T-SQL musst du die ONKZ-Tabelle mit einem less-than Vergleich an deiner normierten Nummer zerlegen, die Ordnung des Zwischenergebnisses umkehren und mit TOP 1 greifst du dann auf die korrekte Vorwahl zu.

Grüße vom marabu

Danke, das funktioniert gut.
Die Funktion läuft aber immer noch 0,4 bis 0,45 Sekunden.

Ich füge hier mal den Code ein, falls jemand mal Bedarf, oder Optimierungsvorschläge hat :angel2:

SQL-Code:

CREATE FUNCTION SYSF_GetSplittedTelNumber (@TelNr VARCHAR(25) )
RETURNS VARCHAR(25)
AS
BEGIN

  DECLARE @TestVorwahl VARCHAR(25)
  DECLARE @TempResult VARCHAR(25)

  IF @TelNr IS NULL OR LTRIM(@TelNr) =''
  BEGIN
    SET @TempResult = ''
  END
  ELSE BEGIN

    SET @TelNr = REPLACE (@TelNr,'-','')
    SET @TelNr = REPLACE (@TelNr,' ','')
    SET @TelNr = REPLACE (@TelNr,'/','')
    SET @TelNr = REPLACE (@TelNr,'(','')
    SET @TelNr = REPLACE (@TelNr,')','')
    SET @TelNr = REPLACE (@TelNr,'_','')
    SET @TelNr = REPLACE (@TelNr,'+','')
    SET @TelNr = REPLACE (@TelNr,'\','')
    SET @TelNr = REPLACE (@TelNr,'"','')
    SET @TelNr = REPLACE (@TelNr,'.','')
    SET @TelNr = REPLACE (@TelNr,':','')
    SET @TelNr = REPLACE (@TelNr,'*','')
    SET @TelNr = REPLACE (@TelNr,'#','')
    SET @TelNr = REPLACE (@TelNr,'p','')
    SET @TelNr = REPLACE (@TelNr,'P','')
    SET @TelNr = REPLACE (@TelNr,',','')

    SET @TestVorwahl = @TelNr
  
    SELECT TOP 1 @Testvorwahl=Vorwahl
    FROM SYSF_Vorwahlen()
    WHERE Vorwahl< @Testvorwahl
    ORDER BY Vorwahl DESC

    SET @TempResult= @TestVorwahl+'-'+RIGHT(@TelNr,LEN(@TelNr)-LEN(@TestVorwahl))

    IF LEFT(@TempResult,1)='-' SET @TempResult=RIGHT(@TempResult,LEN(@TempResult)-1)

  END

  RETURN @TempResult

END

Schöne Grüße,
Jens
:hi:

[Jelly]

Zitat:

Zitat von jensw_2000

Ich füge hier mal den Code ein, falls jemand mal Bedarf, oder Optimierungsvorschläge hat :angel2:

Einen Verbesserungsvorschlag hätte ich, und zwar bezüglich diesem Abschnitt:

SQL-Code:

    SET @TelNr = REPLACE (@TelNr,'-','')
    SET @TelNr = REPLACE (@TelNr,' ','')
    SET @TelNr = REPLACE (@TelNr,'/','')
    SET @TelNr = REPLACE (@TelNr,'(','')
    SET @TelNr = REPLACE (@TelNr,')','')
    SET @TelNr = REPLACE (@TelNr,'_','')
    SET @TelNr = REPLACE (@TelNr,'+','')
    SET @TelNr = REPLACE (@TelNr,'\','')
    SET @TelNr = REPLACE (@TelNr,'"','')
    SET @TelNr = REPLACE (@TelNr,'.','')
    SET @TelNr = REPLACE (@TelNr,':','')
    SET @TelNr = REPLACE (@TelNr,'*','')
    SET @TelNr = REPLACE (@TelNr,'#','')
    SET @TelNr = REPLACE (@TelNr,'p','')
    SET @TelNr = REPLACE (@TelNr,'P','')
    SET @TelNr = REPLACE (@TelNr,',','')

Was, wenn in der TelNr ein 'X' steht, das wird nicht berücksichtigt. Also statt einzelne Zeichen zu entfernen, wieso sorgst du nicht dafür, dass du nur die Ziffern betrachtest.

Und wie in diesem Thread gerade bemerkt wurde, würde ich auch ein <= Vergleich heranziehen, anstatt ein <.

[jensw_2000]

Die akuelle Funktion schieht bei mit so aus ...
Sie ist relativ schnell und läuft stabil ...

SQL-Code:

CREATE FUNCTION SYSF_GetSplittedTelNumber (@TelNr VARCHAR(25) )
RETURNS VARCHAR(25)
AS
BEGIN

  DECLARE @TestVorwahl VARCHAR(25)
  DECLARE @Dummy VARCHAR(25)
  DECLARE @TempResult VARCHAR(25)

  SET @TelNr = REPLACE (@TelNr,'-','')
  SET @TelNr = REPLACE (@TelNr,' ','')
  SET @TelNr = REPLACE (@TelNr,'/','')
  SET @TelNr = REPLACE (@TelNr,'(','')
  SET @TelNr = REPLACE (@TelNr,')','')
  SET @TelNr = REPLACE (@TelNr,'_','')
  SET @TelNr = REPLACE (@TelNr,'+','')
  SET @TelNr = REPLACE (@TelNr,'\','')
  SET @TelNr = REPLACE (@TelNr,'"','')
  SET @TelNr = REPLACE (@TelNr,'.','')
  SET @TelNr = REPLACE (@TelNr,':','')
  SET @TelNr = REPLACE (@TelNr,'*','')
  SET @TelNr = REPLACE (@TelNr,'#','')
  SET @TelNr = REPLACE (@TelNr,'@','')
  SET @TelNr = REPLACE (@TelNr,'%','')
  SET @TelNr = REPLACE (@TelNr,'¦','')
  SET @TelNr = REPLACE (@TelNr,'$','')
  SET @TelNr = REPLACE (@TelNr,'&','')
  SET @TelNr = REPLACE (@TelNr,',','')
  SET @TelNr = REPLACE (@TelNr,'-','')
  SET @TelNr = REPLACE (@TelNr,'/','')
  SET @TelNr = REPLACE (@TelNr,'(','')
  SET @TelNr = REPLACE (@TelNr,')','')
  SET @TelNr = REPLACE (@TelNr,'a','')
  SET @TelNr = REPLACE (@TelNr,'b','')
  SET @TelNr = REPLACE (@TelNr,'c','')
  SET @TelNr = REPLACE (@TelNr,'d','')
  SET @TelNr = REPLACE (@TelNr,'e','')
  SET @TelNr = REPLACE (@TelNr,'f','')
  SET @TelNr = REPLACE (@TelNr,'g','')
  SET @TelNr = REPLACE (@TelNr,'h','')
  SET @TelNr = REPLACE (@TelNr,'i','')
  SET @TelNr = REPLACE (@TelNr,'j','')
  SET @TelNr = REPLACE (@TelNr,'k','')
  SET @TelNr = REPLACE (@TelNr,'l','')
  SET @TelNr = REPLACE (@TelNr,'m','')
  SET @TelNr = REPLACE (@TelNr,'n','')
  SET @TelNr = REPLACE (@TelNr,'o','')
  SET @TelNr = REPLACE (@TelNr,'p','')
  SET @TelNr = REPLACE (@TelNr,'q','')
  SET @TelNr = REPLACE (@TelNr,'r','')
  SET @TelNr = REPLACE (@TelNr,'s','')
  SET @TelNr = REPLACE (@TelNr,'t','')
  SET @TelNr = REPLACE (@TelNr,'u','')
  SET @TelNr = REPLACE (@TelNr,'v','')
  SET @TelNr = REPLACE (@TelNr,'w','')
  SET @TelNr = REPLACE (@TelNr,'x','')
  SET @TelNr = REPLACE (@TelNr,'y','')
  SET @TelNr = REPLACE (@TelNr,'z','')
  SET @TelNr = REPLACE (@TelNr,'A','')
  SET @TelNr = REPLACE (@TelNr,'B','')
  SET @TelNr = REPLACE (@TelNr,'C','')
  SET @TelNr = REPLACE (@TelNr,'D','')
  SET @TelNr = REPLACE (@TelNr,'E','')
  SET @TelNr = REPLACE (@TelNr,'F','')
  SET @TelNr = REPLACE (@TelNr,'G','')
  SET @TelNr = REPLACE (@TelNr,'H','')
  SET @TelNr = REPLACE (@TelNr,'I','')
  SET @TelNr = REPLACE (@TelNr,'J','')
  SET @TelNr = REPLACE (@TelNr,'K','')
  SET @TelNr = REPLACE (@TelNr,'L','')
  SET @TelNr = REPLACE (@TelNr,'M','')
  SET @TelNr = REPLACE (@TelNr,'N','')
  SET @TelNr = REPLACE (@TelNr,'O','')
  SET @TelNr = REPLACE (@TelNr,'P','')
  SET @TelNr = REPLACE (@TelNr,'Q','')
  SET @TelNr = REPLACE (@TelNr,'R','')
  SET @TelNr = REPLACE (@TelNr,'S','')
  SET @TelNr = REPLACE (@TelNr,'T','')
  SET @TelNr = REPLACE (@TelNr,'U','')
  SET @TelNr = REPLACE (@TelNr,'V','')
  SET @TelNr = REPLACE (@TelNr,'W','')
  SET @TelNr = REPLACE (@TelNr,'X','')
  SET @TelNr = REPLACE (@TelNr,'Y','')
  SET @TelNr = REPLACE (@TelNr,'Z','')


  IF @TelNr IS NULL OR LEN(LTRIM(@TelNr))<4
  BEGIN
    SET @TempResult = ''
  END
  ELSE BEGIN

    SET @TestVorwahl = @TelNr
  
    SELECT TOP 1 @Testvorwahl=Vorwahl
    FROM SYSF_Vorwahlen()
    WHERE Vorwahl< @Testvorwahl
    ORDER BY Vorwahl DESC

    SET @Testvorwahl = ISNULL(@Testvorwahl,'')

    IF LEFT(@TelNr,LEN(@Testvorwahl))<>@Testvorwahl SET @Testvorwahl=''

    SET @TempResult= @TestVorwahl+'-'+RIGHT(@TelNr,LEN(@TelNr)-LEN(@TestVorwahl))

    IF LEFT(@TempResult,1)='-' SET @TempResult=RIGHT(@TempResult,LEN(@TempResult)-1)

  END

  RETURN @TempResult

END

Wichtig ist auch, das man prüft, ob die Rufnummer überhaupt mit der gefundenen Vorwahl beginnt. Sonst gibt es bei ungültigen Rufnummern(die nicht mit einer gültigen Vorwahl anfangen) "Zahlensalat", weil einfach die nächst kleinere Vorwahl zurückgegeben wird.

Zitat:

Also statt einzelne Zeichen zu entfernen, wieso sorgst du nicht dafür, dass du nur die Ziffern betrachtest.

Dafür habe ich leider noch keine passende SQL-Funkttion gefunden ...
Wie geht das ?







Schöne Grüße,
Jens
:hi:

[Jelly]

Zitat:

Zitat von jensw_2000

Zitat:

Also statt einzelne Zeichen zu entfernen, wieso sorgst du nicht dafür, dass du nur die Ziffern betrachtest.

Dafür habe ich leider noch keine passende SQL-Funkttion gefunden ...
Wie geht das ?

Eine direkte Funktion dafür kenn ich auch nicht, aber Du könntest doch folgendes verwenden:

SQL-Code:

set @Dummy = @TelNr
set @TelNr = ''
set @N = len(@Dummy)
set @i = 1
while @i <= @N BEGIN
   if substring (@Dummy,@i,@N) between '0' and '9' BEGIN
      set @TelNr = @TelNr + substring(@Dummy,@i,@N)
   END
END

EDIT: Wieso ist hier die Variable Dummy fett geschrieben :gruebel:

[robroy85]

Zitat:

Zitat von Jelly

EDIT: Wieso ist hier die Variable Dummy fett geschrieben :gruebel:

:warn:
Weil "Dummy" zu den reservierten Schlüsselwörtern von T-SQL gehört!
Genau so wie "ADD", "ALL", "AlTER"... naja guckt lieber selber in der T-SQL-Hilfe nach.


Schönen Gruß

Robert

[Jelly]

Zitat:

Zitat von robroy85

:warn:
Weil "Dummy" zu den reservierten Schlüsselwörtern von T-SQL gehört!
Genau so wie "ADD", "ALL", "AlTER"... naja guckt lieber selber in der T-SQL-Hilfe nach.

Das wusste ich zwar nicht, aber dennoch benutze ich jedoch @Dummy und nicht Dummy, und meine Variable darf ich ruhig so nennen, und das hat wiederum nix mit einem Schlüsselwort zu tun.

[robroy85]

@Jelly

Ich denke einfach, dass die DP-Scripts nach diesen Schlüsselwörtern suchen und diese unabhänigig von der davorstehenden Variablendeklaration "@" blau markieren.

Befindet man sich aber im SQL Server Query Analyzer so gibt eine einen Unterschied zwischen "@dummy" und "dummy".
Ersteres ist schwarz, zweiteres blau gefärbt.
Also ist sicher auch für die Zukunft nichts gegen einzuwenden.

Schönen Gruß

[freak4fun]

Hallo :hi:,
so weit ich weiß gibt es Firmen/Leute die ihre Telefonnummer als Name angeben. Zum Beispiel +49 (0)6103 BORLAND Soll heißen, das wenn man die Buchstaben drückt die Nummer entsteht. Ich weiß nicht ob sowas vorkommen kann, aber wollte es doch erwähnt haben. ;)

MfG
freak