Convert alphanumeric phone numbers to Numeric

by Amol 17. February 2011 18:59

Most of the phone numbers are represented in alphanumeric format to remember easily. For example, 1-800-MSFT .  You can see many such numbers in USA. Here I am writing T-SQL to convert alphanumeric numbers to real numbers. On the phone keypad we can see a to z characters to represent its equivalent numeric number.  For example, number 2 represent a, b and c.  A variable @NumberFormat holds all the numbers in sequence of characters.  'abcdefghijklmnopqrstuvwxyz' = '22233344455566677778889999' 

 

-- script to convert alphanumeric phone numbers to numeric

DECLARE @PhoneNumber VARCHAR(32) = '1-800-MSFT'

 

DECLARE @PhoneLength TINYINT = LEN(@PhoneNumber)

DECLARE @Id TINYINT = 1,

        @NumberFormat VARCHAR(26) = '22233344455566677778889999',

        @NumericNumber VARCHAR(32);

 

WITH ConvertToNumber AS

(

      SELECT SUBSTRING(@PhoneNumber, @Id, 1) AS [Numbers],

             @Id + 1 AS Id

      UNION ALL

      SELECT SUBSTRING(@PhoneNumber, Id, 1) AS [Numbers],

             Id + 1 AS Id

      FROM ConvertToNumber

      WHERE Id <= @PhoneLength

)

SELECT @NumericNumber =

       COALESCE(@NumericNumber + '', '') +

       CASE WHEN ASCII([Numbers]) BETWEEN 65 AND 90

THEN SUBSTRING(@NumberFormat, (ASCII([Numbers])-64), 1)

            ELSE [Numbers]

       END

FROM ConvertToNumber

SELECT @NumericNumber AS PhoneNumber

 

PhoneNumber

--------------------------------

1-800-6738


 

Tags:

SQL Server Scripts

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud