Check and cleanse a UK Telephone Number in SQL Server

Create a function in SQL Server to check and cleanse a UK Telephone Number.

Context

One of the most common challenges we have in our working environment is with badly formatted telephone numbers, we are sure we are not alone in that respect. 

They tend to get into bad condition, because they have been run through Excel or various other scenarios changing the output.

This function is targeted mainly at UK numbers, however could be adapted for any other region quite easily. This has been in use for a number of years, and has salvaged countless telephone numbers.

SQL

CREATE FUNCTION [dbo].[CleanTelNo](@TelNo VARCHAR(20))

RETURNS VARCHAR(20)

AS BEGIN

DECLARE @OldTelNo VARCHAR(20)=@TelNo

DECLARE @CorrectTel VARCHAR(20)

 

SET @CorrectTel =(CASE WHEN @TelNo='0'THEN 1 ELSE 0 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 =(CASE

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 14 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 @TelNo

END

GO

Ousia Logo