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)


        @NumberFormat VARCHAR(26) = '22233344455566677778889999',

        @NumericNumber VARCHAR(32);


WITH ConvertToNumber AS


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

             @Id + 1 AS Id


      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]


FROM ConvertToNumber

SELECT @NumericNumber AS PhoneNumber







SQL Server Scripts

Tag cloud