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