SQLServerの電話番号のクレンジング
SQL Server関数を作成して、国際または英国ベースの電話番号文字列をチェックおよびクレンジングします
環境
クライアントインフラストラクチャで直面する最も一般的な課題の1つは、電話番号の形式が正しくないことです。 Excelやその他のさまざまなシナリオで出力を変更すると、状態が悪くなる傾向があります。
この関数は、国際番号の長さにある程度の範囲がある英国の番号を対象としています。最後のcaseステートメントの最初の6つのケース項目と、最後から2番目のcaseステートメントの3つのケース項目を適合させることにより、他の地域に非常に簡単に適合させることができます。
これは何年もの間使用されており、次の手順を使用して無数の電話番号を回収してきました。
- 数値を一時変数にバックアップします
- 開始記号をダブルゼロに置き換えます
- 文字列から残りのテキスト文字をすべて削除します
- 英国のダイヤルイン番号を0に置き換えます
- 各数値タイプの長さを確認してください。
- 新しい番号を返すか、検証できなかった場合は古い番号を返します。
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