larsbloch
9/19/2018 - 11:52 AM

SQL Clean Phone numbers

Will clean phone numbers based on a country code

CREATE FUNCTION [dbo].[ufnCleanPhoneNumber](@Number nvarchar(50), @CountryCode nvarchar(10))  
RETURNS nvarchar(50)   
AS   
BEGIN
	--Check input number for illegal characters
	IF @Number is null RETURN ''
	IF @Number LIKE '%[,/\.&]%' RETURN 'Xillegal char'
		
--CreateTrimmedNumber
DECLARE @TNumber nvarchar(50); SELECT @TNumber = REPLACE(@Number, ' ', '')
IF @TNumber = '' OR @TNumber = '0' RETURN '' --if empty then return empty
		
     IF @CountryCode = 'DK' -----------------DENMARK---------------------
	 BEGIN
		IF  @TNumber LIKE '%-%' SET @TNumber ='Xillegal char'
		ELSE IF @TNumber NOT LIKE '+45%'  SET @TNumber = '+45'  + @TNumber 
	 END
ELSE IF @CountryCode = 'SE'------------------SWEDEN-----------------------------
	BEGIN 
		IF @TNumber NOT LIKE '+46%' SET @TNumber = '+46'  + @TNumber 
	END
ELSE IF @CountryCode = 'NO'-----------------NORWAY-------------------------------
	BEGIN 
		IF @TNumber NOT LIKE '+47%' SET @TNumber = '+47'  + @TNumber 
	END
ELSE IF @CountryCode = 'FI'---------------------FINLAND--------------------
	BEGIN
		IF @TNumber NOT LIKE '+358%' SET @TNumber = '+358'  + @TNumber 
	END
ELSE IF @CountryCode = 'GB'-------------UNITED KINGDOM--------------------------
	BEGIN 
		IF @TNumber NOT LIKE '+44%' SET @TNumber = '+44'  + @TNumber 
	END
Else SET @TNumber = 'Unk countrycd:' + @CountryCode

    RETURN @TNumber;  
END
GO