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