dbo.fn_CleanPhoneNo

USE <database_name>;
GO
IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N'dbo.fn_CleanPhoneNo')
AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
DROP FUNCTION dbo.fn_CleanPhoneNo;
END
GO
CREATE FUNCTION dbo.fn_CleanPhoneNo
(
@sPhoneNo VARCHAR(40),
@iDefaultAreaCode INT = NULL
-- worst case input scenario is something like 1-212-456-7890 extension 654967
-- worst case output scenario is something like 212-456-7890 x654967
-- seven-digit or greater extension numbers should not exist
)
RETURNS VARCHAR(20)
AS
/*
*****************************************************************************
Created By: Barry Blessing
-------------------------------------------------------------------------------
Date Created: 2/24/2016
-------------------------------------------------------------------------------
Purpose: Homogenize US-only phone number data, including numbers with extension no's
Database: Yardi
-------------------------------------------------------------------------------
Parameters: @sPhoneNo -- any string representing a US phone number.

Assumptions: AREA CODE IS FIRST SIGNIFICANT THREE INTEGERS,
ASIDE FROM A LEADING ONE OR ZERO

Ideally, extensions would be in a separate table column, but if
the phone number is stored as a string, this is almost never the case.
-------------------------------------------------------------------------------
Performance Statistics:
*******************************************************************************
Date Ran: 2/24/2017

Test Statements ran:

SELECT dbo.fn_CleanPhoneNo('0 249 7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('1 249 7890 ', '712')
SELECT dbo.fn_CleanPhoneNo(' 249 7890 ', '202')
SELECT dbo.fn_CleanPhoneNo(' 249 7890 ', '618')
SELECT dbo.fn_CleanPhoneNo('0 249 7890 x 345 ', '605')
SELECT dbo.fn_CleanPhoneNo('1 249 7890 x 345 ', '319')

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x654967', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 654967', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x65496', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 65496', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6549', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6549', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x654', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 654', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x65', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 65', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890 ', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 249 7890 ', DEFAULT)

SELECT dbo.fn_CleanPhoneNo('1-212-456-7890 extension 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('12124567890 ext 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('02124567890 ext 6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0-212-456-7890x6549678', DEFAULT)
SELECT dbo.fn_CleanPhoneNo('0 212 456 7890 x 6549678', DEFAULT)

Run time in seconds: 0, per SELECT statement above
9100 non-null rows in 0.00xx seconds; only 1146 of which had actual phone numbers
27978 rows in 0.00xx seconds; only 1146 of which had actual phone numbers
*******************************************************************************
Maintenance Log:
*******************************************************************************
Date Version PCM Author Comments
-------------------------------------------------------------------------------
2/24/2017 1.00 Barry Blessing Initial Version
*******************************************************************************
*/
BEGIN

DECLARE @sExtension VARCHAR(7) = NULL;
DECLARE @sExtensionStartPos INT = 0;

IF @sPhoneNo IS NULL
OR LTRIM(RTRIM(@sPhoneNo)) = ''
OR LEN(LTRIM(RTRIM(@sPhoneNo))) <= 7
BEGIN
RETURN LTRIM(RTRIM(ISNULL(@sPhoneNo, '')));
END

SET @sPhoneNo = LOWER(LTRIM(RTRIM(@sPhoneNo)));

/* 'x' will be our 'extension' delimiter */
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extension', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extnsn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extens', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'exten', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'xtsn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'extn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'xtn', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'ext', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'ex', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, 'x', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, ',', ' x');
SET @sPhoneNo = REPLACE(@sPhoneNo, '-', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '.', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '/', ''); /* usually a ten-key error */
SET @sPhoneNo = REPLACE(@sPhoneNo, '\', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '|', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, '(', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, ')', ''); /* usually intentional */
SET @sPhoneNo = REPLACE(@sPhoneNo, ' ', ' ');
SET @sPhoneNo = REPLACE(@sPhoneNo, ' ', ''); /* usually intentional */
SET @sPhoneNo = LTRIM(RTRIM(@sPhoneNo));

SET @sExtensionStartPos = CHARINDEX('x', @sPhoneNo);

/* grab the extension and save it */
SET @sExtension = CASE WHEN @sExtensionStartPos > 0 THEN
LTRIM(RTRIM(
SUBSTRING( @sPhoneNo,
@sExtensionStartPos,
(LEN(LTRIM(RTRIM(@sPhoneNo))) - @sExtensionStartPos + 1)
)
))
ELSE
NULL
END;

/* rid the phone number of the extension */
SET @sPhoneNo = CASE WHEN @sExtensionStartPos > 0 THEN
LTRIM(RTRIM(SUBSTRING(@sPhoneNo, 1, @sExtensionStartPos - 1)))
ELSE
LTRIM(RTRIM(REPLACE(@sPhoneNo, 'x', '')))
END;

/* strip all non-integers out of the remaining phone number string */
WHILE PATINDEX('%[^0-9]%', @sPhoneNo) > 0
BEGIN
SET @sPhoneNo = LTRIM(RTRIM(
STUFF(
@sPhoneNo,
PATINDEX(
'%[^0-9]%',
@sPhoneNo),
1,
'')
));
END;

/* rid the extension number-string of the 'x' */
SET @sExtension = LTRIM(RTRIM(REPLACE(@sExtension, 'x','')));

/* trim off leading ones or zeros from the main phone number string */
WHILE SUBSTRING(LTRIM(RTRIM(@sPhoneNo)), 1, 1) = '0' OR
SUBSTRING(LTRIM(RTRIM(@sPhoneNo)), 1, 1) = '1'
BEGIN
SET @sPhoneNo = LTRIM(RTRIM(
SUBSTRING( LTRIM(RTRIM(@sPhoneNo)),
2,
LEN(LTRIM(RTRIM(@sPhoneNo)))
)
));
END;

/* assume an area code for all phone numbers entered without an area-code */
SET @sPhoneNo = CASE WHEN @iDefaultAreaCode IS NOT NULL
AND LTRIM(RTRIM(@iDefaultAreaCode)) <> 0
AND @iDefaultAreaCode IS NOT NULL
AND LEN(@sPhoneNo) = 7
AND LEN(@iDefaultAreaCode) = 3 THEN

CAST(@iDefaultAreaCode AS VARCHAR(3)) + @sPhoneNo

WHEN @iDefaultAreaCode IS NULL
OR LTRIM(RTRIM(@iDefaultAreaCode)) = 0
OR LEN(@iDefaultAreaCode) <= 2 THEN

--CASE WHEN LEN(@sPhoneNo) < 10 THEN
-- '515' + @sPhoneNo
-- ELSE
-- @sPhoneNo
--END

@sPhoneNo
ELSE
@sPhoneNo
END;

/* grab an extension where it was entered without an 'x|ext'-style delimiter; where not already parsed out */

SET @sExtension = CASE WHEN (@sExtension IS NULL OR @sExtension = '') AND LEN(@sPhoneNo) > 10 THEN
SUBSTRING( @sPhoneNo,
11,
LEN(@sPhoneNo) - 10
)
ELSE
@sExtension
END;

/* format the phone number return string (which will not contain an extension #), including the extension where applicable */
SET @sPhoneNo = CASE WHEN LEN(@sPhoneNo) = 7
AND LTRIM(RTRIM(@sPhoneNo)) <> ''
AND @sPhoneNo IS NOT NULL
AND ISNUMERIC(@sPhoneNo) = 1 THEN
CASE WHEN @sExtension IS NOT NULL
AND @sExtension <> '' THEN
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 4) + ' x' + @sExtension
ELSE
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 4)
END
WHEN LEN(@sPhoneNo) BETWEEN 10 AND 16
AND LTRIM(RTRIM(@sPhoneNo)) <> ''
AND @sPhoneNo IS NOT NULL
AND ISNUMERIC(@sPhoneNo) = 1 THEN
CASE WHEN @sExtension IS NOT NULL
AND @sExtension <> '' THEN
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 3) + '-' + SUBSTRING(@sPhoneNo, 7, 4) + ' x' + @sExtension
ELSE
SUBSTRING(@sPhoneNo, 1, 3) + '-' + SUBSTRING(@sPhoneNo, 4, 3) + '-' + SUBSTRING(@sPhoneNo, 7, 4)
END
END;

RETURN @sPhoneNo;
END
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s