Nice work
Steven Willis (12/7/2010)
Someone recently posted a function like the one below that I have modified to validate US/Canadian format phone numbers. Sorry that I can't remember the OP to give proper credit. But this works well for me. Obviously, it would have to be modified to handle international formats.
/*
SELECT dbo.ufnFormatUSPhone('7044567890')-- Valid Number
SELECT dbo.ufnFormatUSPhone('17044567890')-- Valid Number without initial '1'
SELECT dbo.ufnFormatUSPhone('4567890')-- Valid Number without area code
SELECT dbo.ufnFormatUSPhone('24567890')-- Returns NULL
SELECT dbo.ufnFormatUSPhone('123456789099999')-- Returns NULL
SELECT dbo.ufnFormatUSPhone('abc')-- Returns NULL
*/
ALTER FUNCTION [dbo].[ufnFormatUSPhone]
(
@phonenumber VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
/** Remove White Space and non-Integer(s) values **/
WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0
BEGIN
SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')
END
SET @phonenumber = LTRIM(RTRIM(@phonenumber))
/** Get the number of digits **/
DECLARE @NumChars INT
SET @NumChars = LEN(@phonenumber)
/** Check to see if this number has a leading value of '1' **/
IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'
BEGIN
SET @phonenumber = RIGHT(@phonenumber,10)
SET @NumChars = LEN(@phonenumber)
END
/** If valid number of digits proceed with formatting **/
IF @NumChars = 7 OR @NumChars = 10
BEGIN
SET @phonenumber = REVERSE(@phonenumber)
/** Format Phone Number **/
SET @phonenumber =
REVERSE(LEFT(@phonenumber,4)
+ '-'
+ SUBSTRING(@phonenumber,5,3)
+ COALESCE(' )'
+ NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))
END
ELSE
SET @phonenumber = NULL
RETURN @phonenumber
END