SQL Server'da Telefon Numaralarını Temizleme
Uluslararası veya Birleşik Krallık tabanlı bir Telefon Numarası dizesini kontrol etmek ve temizlemek için bir SQL Server işlevi oluşturun
Bağlam
İstemci altyapısında karşılaştığımız en yaygın zorluklardan biri, hatalı biçimlendirilmiş telefon numaralarıdır. Excel veya çıktıyı değiştiren diğer çeşitli senaryolar aracılığıyla çalıştırıldıklarında kötü bir duruma girme eğilimindedirler.
Bu işlev, uluslararası sayıların uzunlukları için bazı kapsamlarla birlikte Birleşik Krallık numaralarına yöneliktir. Son vaka ifadesindeki ilk 6 vaka maddesini ve ikinci son vaka ifadesindeki üç vaka maddesini uyarlayarak diğer herhangi bir bölge için oldukça kolay bir şekilde uyarlanabilir.
Bu, birkaç yıldır kullanılmaktadır ve aşağıdaki adımları kullanarak sayısız telefon numarasını kurtarmıştır;
- Numarayı geçici bir değişkene yedekleyin
- Başlangıç işaretini çift sıfırla değiştirin
- Dizeden kalan metin karakterlerini kaldırın
- İngiltere'deki arama numaralarını 0 ile değiştirin
- Her sayı türünün uzunluğunu kontrol edin.
- Doğrulanamıyorsa yeni numarayı veya eski numarayı iade edin.
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