Parse Phone Info
This function accepts a phone number as a string and does its best to tear it apart and return the various pieces (e.g. area code, last four, etc.) It returns the phone number in a format specified by the parameters passed in.
First, do the necessary code cleanup / removal (to ease the update / debugging process):
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_GetPhoneInfo]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fn_GetPhoneInfo]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_LetterToPhoneNums]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fn_LetterToPhoneNums]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_TrimExtras]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fn_TrimExtras]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Then create the main function:
Inputs:
- @GetWhat: Specifies what part(s) of the extracted phone number to return (see comments in code header).
- @FormatAs: Specifies how to format the text that is returned (see comments in code header).
- @ConvertLetters: If 1, then letters will be converted to numbers (e.g. any of "abcABC" will become '1').
- @DefaultAreaCode: Specifies what area code to use if one is not found in the @input.
- @Input: String of text to pull phone number from.
Returns:
- Reformatted text based on @GetWhat and @FormatAs parameters.
Assumes:
- Parameters are not garbage-text.
- Input contains only one phone number.
Code:
/*Description: Gets specified phone information out of a string with unspecified formatting
DATE BY CHANGE
3/28/2003 Sean P. O. MacCath-Moran Created
*/
CREATE FUNCTION dbo.fn_GetPhoneInfo
(
@GetWhat AS int = 1, /*1 = Full Phone Number w/ Extension
2 = Full Phone Number w/o Extension
3 = 7 Digit Phone w/ Extension
4 = 7 Digit Phone w/o Extension
5 = Area Code
6 = Extension
*/
@FormatAs AS int = 1, /*1 = Standard 1: (999)999-9999x9999
2 = Standard 2: (999) 999-9999 x99999
3 = Dashes: 999-999-9999x99999
4 = Raw: 999999999999999
*/
@ConvertLetters AS bit = 1, /* If 1, then letters will be converted to numbers*/
@DefaultAreaCode AS varchar(3) = NULL, /* Area code to use if none found */
@Input AS varchar(3000)
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @ReturnVal varchar(1000), @FindChar int, @Len int, @ExtLen int
DECLARE @Phone varchar(10), @Ext varchar(10), @AreaCode varchar(10), @GetWhatStr varchar(1), @FormatAsStr varchar(1)
DECLARE @AssumeCountry varchar(3)
DECLARE @FoundFlag int /* This flags what has been found so far, mirroring the @GetWhat flags indicated above*/
DECLARE @AddressLine1 varchar(1000), @AddressLine2 varchar(1000), @AddressLine3 varchar(1000)
DECLARE @City varchar(100), @StateAbrv varchar(3), @StateLong varchar(50), @PostalCode varchar(10), @Country varchar(3)
DECLARE @3DigitPattern varchar(20), @4DigitPattern varchar(20), @5DigitPattern varchar(20)
DECLARE @AreaCodePattern varchar(20), @Ext1Pattern varchar(20), @Ext2Pattern varchar(20), @Ext3Pattern varchar(20)
DECLARE @USAPattern1 varchar(100), @USAPattern2 varchar(100), @USAPattern3 varchar(100), @CANPattern1 varchar(100), @CANPattern2 varchar(100), @AUSPattern1 varchar(100)
/*Test for conditions that can never return a value. Return a NULL if they are met.*/
IF @Input IS NULL OR dbo.fn_TrimExtras(1, @Input) = '' OR (@GetWhat < 1 OR @GetWhat > 6) OR (@FormatAs < 1 OR @FormatAs > 3) RETURN NULL
/*Remove surrounding spaces*/
SET @Input = RTRIM(LTRIM(@Input))
/*Determine if parenthesees mark an area code to be retrieved.*/
SELECT @Len = CHARINDEX('(', @Input, 1), @ExtLen = CHARINDEX(')', @Input, 1)
IF @ExtLen - @Len = 4 AND LEN(@Input) - @ExtLen >= 7
BEGIN
SET @AreaCode = SUBSTRING(@Input, @Len + 1, 3) /*Save Area Code*/
SET @Input = SUBSTRING(@Input, @ExtLen + 1, 1000) /*Truncate Remainder*/
END
SET @Len = LEN(@Input)
SET @FindChar = 0
/*Remove all non alpha numerics from the string*/
WHILE 1 = 1
BEGIN
SET @FindChar = @FindChar + 1
IF @Len <= @FindChar BREAK
IF PATINDEX('[a-z,0-9]', SUBSTRING(@Input, @FindChar, 1)) = 1 CONTINUE
SET @Input = STUFF (@Input, @FindChar, 1, '') /*Remove non alphanumeric*/
SET @Len = @Len - 1 /*Decrease the size of Len*/
SET @FindChar = @FindChar - 1 /*Set @FindChar to point to next char*/
END
IF LEN(@Input) = 0 GOTO ExitSP
/*If the first digit is a 1 and there are more than 10 digits then assume there is an area
code following the 1. Either way, truncate the 1.*/
IF SUBSTRING(@Input, 1, 1) = '1' SET @Input = SUBSTRING(@Input, 2, 1000)
IF LEN(@Input) >= 10 AND LEN(@AreaCode) = 0
BEGIN
SET @AreaCode = SUBSTRING(@Input, 1, 3) /*Save Area Code*/
SET @Input = SUBSTRING(@Input, 4, 1000) /*Truncate Remainder*/
END
/*If the value just pulled is empty then move on to next field*/
IF LEN(@Input) = 0 GOTO ExitSP
/*If there are more chars present then the standard 7 digits then attempt to locate an extension indicator*/
IF LEN(@Input) <= 7
/*Input is 7 chars or less, so assume that it is the phone number.*/
SET @Phone = @Input
ELSE /*Parse out the remaining number*/
BEGIN
SET @Len = LEN(@Input)
SET @FindChar = 8
/*If there is an X present then check to see if:
The x occures after the standard 7 (123-SNOWxICE)
The x occures with at least three digits after it*/
WHILE 1=1
BEGIN
SET @FindChar = CHARINDEX('x', @Input, @FindChar)
IF @FindChar = 0 BREAK
IF (LEN(@Input)-@FindChar) >= 3 BREAK
SET @FindChar = @FindChar + 1
END
/*If not apparent extension was found then just convert all of the letters*/
IF @FindChar > 0
BEGIN
SET @ExtLen = 1
IF SUBSTRING(@Input, @FindChar-1, 3) = 'ext'
BEGIN
SET @ExtLen = 3
SET @FindChar = @FindChar-1
END
IF SUBSTRING(@Input, @FindChar-1, 2) = 'ex'
BEGIN
SET @ExtLen = 2
SET @FindChar = @FindChar-1
END
SET @Ext = SUBSTRING(@Input, @FindChar+@ExtLen, 1000)
SET @Phone = SUBSTRING(@Input, 1, @FindChar-1)
END
ELSE
BEGIN
/*If AreaCode has not been populated and there are enough chars to make a phone
number and area code, then assume there is an area code.*/
IF LEN(@AreaCode) = 0 AND LEN(@Input) > 9 /*test for possible area code*/
BEGIN
SET @AreaCode = SUBSTRING(@Input, 1, 3)
SET @Phone = SUBSTRING(@Input, 4, 7)
SET @Ext = SUBSTRING(@Input, 11, 1000)
END
ELSE
BEGIN
SET @Phone = SUBSTRING(@Input, 1, 7)
SET @Ext = SUBSTRING(@Input, 8, 1000)
END
END
END
ExitSP:
SET @AreaCode = COALESCE(CASE WHEN LEN(@AreaCode) = 0 THEN NULL ELSE @AreaCode END, CASE WHEN LEN(@DefaultAreaCode) = 0 THEN NULL ELSE @DefaultAreaCode END, '000')
SET @Phone = COALESCE(@Phone, '0000000')
SET @GetWhatStr = LTRIM(STR(@GetWhat))
SET @FormatAsStr = LTRIM(STR(@FormatAs))
/*If letters are being converted to numbers...*/
IF @ConvertLetters = 1
BEGIN
SET @Phone = dbo.fn_LetterToPhoneNums(@Phone)
SET @AreaCode = dbo.fn_LetterToPhoneNums(@AreaCode)
SET @Ext = dbo.fn_LetterToPhoneNums(@Ext)
END
IF @FormatAs = 1 SET @AreaCode = '(' + @AreaCode + ')'
IF PATINDEX('[123]', @FormatAsStr) = 1
BEGIN
IF LEN(@Ext) > 0
BEGIN
SET @Ext = STUFF(@Ext, 1, 0, 'x')
IF @FormatAs = 2 SET @Ext = STUFF(@Ext, 1, 0, ' ')
END
SET @Phone = STUFF(@Phone, 4, 0, '-') /*insert a dash into the phone number*/
END
SET @Input = ''
IF PATINDEX('[125]', @GetWhatStr) = 1 SET @Input = @Input + @AreaCode
IF PATINDEX('[1-4]', @GetWhatStr) = 1 SET @Input = @Input + CASE WHEN LEN(@Input) > 0 THEN CASE @FormatAs WHEN 1 THEN ' ' WHEN 2 THEN '-' ELSE '' END ELSE '' END + @Phone
IF PATINDEX('[136]', @GetWhatStr) = 1 AND @Ext IS NOT NULL SET @Input = @Input + CASE WHEN LEN(@Input) > 0 THEN ' ' ELSE '' END + @Ext
RETURN @Input
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Finally, create the supporting functions:
/*Description: Converts the alpha value is a string to the number that corresponds to them on a telephone keypad
DATE BY CHANGE
5/8/2003 Sean P. O. MacCath-Moran Created
*/
CREATE FUNCTION dbo.fn_LetterToPhoneNums
(
@Input AS varchar(50)
)
RETURNS varchar(50) AS
BEGIN
DECLARE @Len int, @Index int, @Char varchar(1)
SET @Len = LEN(@Input) /*Retrieve lengh of input*/
SET @Index = 0 /*Init index*/
WHILE @Len > @Index /*Loop for the lengh of the string*/
BEGIN
SET @Index = @Index + 1 /*Incement position counter*/
SET @Char = SUBSTRING(@Input, @Index, 1) /*Get char from current position*/
IF PATINDEX('[a-z]', @Char) = 0 CONTINUE /*If this character is not alpha then move on to next value*/
IF PATINDEX('[a-c]', @Char) > 0 /*Replace letter with the appropriate number*/
SET @Char = '2'
ELSE IF PATINDEX('[d-f]', @Char) > 0
SET @Char = '3'
ELSE IF PATINDEX('[g-i]', @Char) > 0
SET @Char = '4'
ELSE IF PATINDEX('[j-l]', @Char) > 0
SET @Char = '5'
ELSE IF PATINDEX('[m-o]', @Char) > 0
SET @Char = '6'
ELSE IF PATINDEX('[p-s]', @Char) > 0
SET @Char = '7'
ELSE IF PATINDEX('[t-v]', @Char) > 0
SET @Char = '8'
ELSE IF PATINDEX('[w-y]', @Char) > 0
SET @Char = '9'
SET @Input = STUFF (@Input, @Index, 1, @Char)
END
RETURN @Input
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*Description: Removes non-character / non-numeric characters from either end of a string
DATE BY CHANGE
3/28/2003 Sean P. O. MacCath-Moran Created
*/
CREATE FUNCTION dbo.fn_TrimExtras
(
@TrimWhat AS int = 1, /*1 = Trim both ends of string
2 = Trim front of string
3 = Trim end of string
4 = Trim from entire string
*/
@String AS varchar(8000)
)
RETURNS varchar(8000) AS
BEGIN
DECLARE @Unicode int, @CurrentLen int
SET @CurrentLen = 0
WHILE @CurrentLen != LEN(@String)
BEGIN
SET @CurrentLen = LEN(@String)
IF @TrimWhat = 1 OR @TrimWhat = 2
BEGIN
SET @Unicode = UNICODE(SUBSTRING(@String, 1, 1))
IF (@Unicode < 48 OR @Unicode > 57)
AND (@Unicode < 65 OR @Unicode > 90)
AND (@Unicode < 97 OR @Unicode > 122)
BEGIN
SET @String = SUBSTRING(@String, 2, 8000)
END
END
IF @TrimWhat = 1 OR @TrimWhat = 3
BEGIN
SET @Unicode = UNICODE(SUBSTRING(@String, LEN(@String), 1))
IF (@Unicode < 48 OR @Unicode > 57)
AND (@Unicode < 65 OR @Unicode > 90)
AND (@Unicode < 97 OR @Unicode > 122)
BEGIN
SET @String = SUBSTRING(@String, 1, LEN(@String)-1)
END
END
END
RETURN @String
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
This work is licenced under a Attribution Non-commercial Share Alike Creative Commons licence