|
Execute the following SQL Server T-SQL
script in SSMS Query Editor to create a phone number cleansing and formatting function; Test it on the Person.Contact table:
USE AdventureWorks;
GO
-- T-SQL user-defined function - UDF - scalar-valued function
-- SQL format phone number string to standard telephone number format
CREATE FUNCTION fnStandardPhone
(@PhoneNumber VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @Phone CHAR(32)
SET @Phone = @PhoneNumber
-- cleanse phone number string
WHILE PATINDEX('%[^0-9]%',@PhoneNumber) > 0
SET @PhoneNumber = REPLACE(@PhoneNumber,
SUBSTRING(@PhoneNumber,PATINDEX('%[^0-9]%',@PhoneNumber),1),'')
-- skip foreign phones
IF (SUBSTRING(@PhoneNumber,1,1) = '1'
OR SUBSTRING(@PhoneNumber,1,1) = '+'
OR SUBSTRING(@PhoneNumber,1,1) = '0')
AND LEN(@PhoneNumber) > 11
RETURN @Phone
-- build US standard phone number
SET @Phone = @PhoneNumber
SET @PhoneNumber = '(' + SUBSTRING(@PhoneNumber,1,3) + ') ' +
SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4)
IF LEN(@Phone) - 10 > 1
SET @PhoneNumber = @PhoneNumber + ' X' + SUBSTRING(@Phone,11,LEN(@Phone) - 10)
RETURN @PhoneNumber
END
GO
SELECT FullName=FirstName + ' ' + LastName,
[Standard Phone] = dbo.fnStandardPhone(Phone),
[Non-Standard] = Phone
FROM Person.Contact
ORDER BY LastName,
FirstName
GO
/*
FullName Standard Phone Non-Standard
Syed Abbas (926) 555-0182 926-555-0182
Catherine Abel (747) 555-0171 747-555-0171
Kim Abercrombie (334) 555-0137 334-555-0137
Kim Abercrombie (208) 555-0114 208-555-0114
.....
*/ |
