Telefonnummern trennen in Vorwahl-Rufnummer mit TSQL

[jensw_2000]

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

[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 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

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

[Jelly]

Zitat von jensw_2000:

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

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

[Jelly]

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

[robroy85]

Zitat von Jelly:

EDIT: Wieso ist hier die Variable Dummy fett geschrieben

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 von robroy85:

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ß