Parse Phone Info

user notice: You have unclosed fn tags. This is invalid and will produce unpredictable results. in /nfs/c07/h01/mnt/112174/domains/emanaton.com/html/sites/all/modules/footnotes/footnotes.module on line 158.

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].') AND xtype IN (N'FN', N'IF', N'TF'))

DROP FUNCTION [dbo].
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].') AND xtype IN (N'FN', N'IF', N'TF'))

DROP FUNCTION [dbo].
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].') AND xtype IN (N'FN', N'IF', N'TF'))

DROP FUNCTION [dbo].
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