پاک کردن شماره تلفن ها در SQL Server
برای بررسی و پاکسازی رشته شماره تلفن بین المللی یا مستقر در انگلیس ، یک تابع SQL Server ایجاد کنید
متن نوشته
یکی از رایج ترین چالش هایی که در زیرساخت های مشتری با آن روبرو هستیم ، شماره تلفن های دارای قالب نادرست است. هنگامی که از طریق اکسل یا سناریوهای مختلف دیگری که باعث تغییر در خروجی می شود ، اجرا می شوند ، معمولاً دچار شرایط بدی می شوند.
این تابع با تعداد دامنه برای اعداد بین المللی با تعداد انگلستان هدف گذاری می شود. با تطبیق 6 مورد اول در بیانیه مورد آخر ، و سه مورد در بیانیه مورد دوم دوم ، می توان آن را برای هر منطقه دیگر به راحتی تطبیق داد.
این برای چندین سال مورد استفاده بوده و با استفاده از مراحل زیر شماره تلفن های بی شماری را نجات داده است.
- از شماره در یک متغیر موقت پشتیبان تهیه کنید
- علامت شروع را با دو صفر جایگزین کنید
- نویسه های متنی باقی مانده را از رشته حذف کنید
- شماره های شماره گیری انگلستان را با 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