Telefonnummern in SQL Server bereinigen
Erstellen Sie eine SQL Server-Funktion, um eine internationale oder britische Telefonnummernzeichenfolge zu überprüfen und zu bereinigen
Kontext
Eine der häufigsten Herausforderungen, denen wir in der Client-Infrastruktur gegenüberstehen, sind schlecht formatierte Telefonnummern. Sie neigen dazu, in einen schlechten Zustand zu geraten, wenn sie Excel oder verschiedene andere Szenarien durchlaufen haben, die die Ausgabe ändern.
Diese Funktion richtet sich an UK-Nummern mit einem gewissen Spielraum für Längen internationaler Nummern. Es könnte ganz einfach für jede andere Region angepasst werden, indem die ersten 6 Fallelemente in der letzten Fallaussage und die drei Fallelemente in der vorletzten Fallaussage angepasst werden.
Dies wird seit einigen Jahren verwendet und hat mit den folgenden Schritten unzählige Telefonnummern gerettet;
- Sichern Sie die Nummer in einer temporären Variablen
- Ersetzen Sie ein Startzeichen durch eine doppelte Null
- Entfernen Sie alle verbleibenden Textzeichen aus der Zeichenfolge
- Ersetzen Sie britische Einwahlnummern durch 0
- Überprüfen Sie die Länge jedes Nummerntyps.
- Geben Sie die neue Nummer zurück oder geben Sie die alte Nummer zurück, wenn sie nicht validiert werden konnte.
SQL
CREATE FUNCTION [dbo].[CleanseTelephone](@TelNo VARCHAR(30))
RETURNS VARCHAR(20)
AS BEGIN
DECLARE @OldNumber VARCHAR(20)=@TelNo
SET @TelNo =(CASE
WHEN LEFT(@TelNo,1)='+' THEN STUFF(@TelNo,1,1,'00')
ELSE @TelNo END)
DECLARE @Letter INT
SET @Letter =PATINDEX('%[^0-9]%',@TelNo)
BEGIN
WHILE @Letter>0
BEGIN
SET @TelNo =STUFF(@TelNo,@Letter,1,'')
SET @Letter =PATINDEX('%[^0-9]%',@TelNo)
END
END
SET @TelNo =(CASEWHEN LEFT(@TelNo,5)='00440' THEN STUFF(@TelNo,1,5,'0')WHEN LEFT(@TelNo,4)='0440' THEN STUFF(@TelNo,1,4,'0')
WHEN LEFT(@TelNo,3)='440' THEN STUFF(@TelNo,1,3,'0')
WHEN LEFT(@TelNo,2)='44' THEN STUFF(@TelNo,1,2,'0')
ELSE @TelNo END)
SET @TelNo =(CASE
WHEN LEFT(@TelNo,2)='01' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='02' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='05' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='07' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='09' AND LEN(@TelNo)=11 THEN @TelNo
WHEN LEFT(@TelNo,2)='08' AND LEN(@TelNo) BETWEEN 10 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,3)='001' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0020' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0021' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0023' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0024' AND LEN(@TelNo) BETWEEN 11 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0025' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0026' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0027' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0030' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0031' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0032' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0033' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0034' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0035' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0036' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0037' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0038' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0039' AND LEN(@TelNo) BETWEEN 14 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0040' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0041' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0042' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0043' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0044' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0045' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0046' AND LEN(@TelNo) BETWEEN 11 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0047' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0048' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0049' AND LEN(@TelNo) BETWEEN 13 AND 16 THEN @TelNo
WHEN LEFT(@TelNo,4)='0050' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0051' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0052' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0053' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0054' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0055' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0056' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0057' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0058' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0059' AND LEN(@TelNo) BETWEEN 11 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0060' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0061' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0062' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0063' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0064' AND LEN(@TelNo) BETWEEN 12 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0065' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0066' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0067' AND LEN(@TelNo) BETWEEN 12 AND 12 THEN @TelNo
WHEN LEFT(@TelNo,4)='0074' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0080' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0081' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0082' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0085' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0086' AND LEN(@TelNo) BETWEEN 15 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0087' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0088' AND LEN(@TelNo) BETWEEN 15 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0090' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0091' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0092' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0093' AND LEN(@TelNo) BETWEEN 12 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0094' AND LEN(@TelNo) BETWEEN 13 AND 13 THEN @TelNo
WHEN LEFT(@TelNo,4)='0095' AND LEN(@TelNo) BETWEEN 11 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,4)='0096' AND LEN(@TelNo) BETWEEN 11 AND 15 THEN @TelNo
WHEN LEFT(@TelNo,4)='0097' AND LEN(@TelNo) BETWEEN 13 AND 14 THEN @TelNo
WHEN LEFT(@TelNo,4)='0098' AND LEN(@TelNo) BETWEEN 11 AND 11 THEN @TelNo
WHEN LEFT(@TelNo,4)='0099' AND LEN(@TelNo) BETWEEN 14 AND 14 THEN @TelNo
ELSE NULL END)
RETURN ISNULL(@TelNo,@OldNumber)
--RETURN @TelNo--Use for returning NULL with non valid numbers
END
GO