Make your own free website on Tripod.com

UDF to convert alphanumeric phone numbers to numeric
Last updated: July 20th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:  
Database coding conventions SQL Server interview questions
Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools
SQL Server security best practices SQL Server administration best practices
Related books:
The guru's guide to Transact SQL Advanced Transact-SQL for SQL Server 2000 SQL Server 2000 Programming by example

User Defined Function (UDF) to convert alphanumeric telephone numbers to numeric numbers


It is very common to use alphabets in phone numbers, in countries like USA. Numbers like "1 800 MICROSOFT", "1 425 MSFAX", "1 800 GO ALAMO" are far too common in States. This often confuses new entrants in USA, and people from other countries, as they have no clue how to convert those characters to numbers!

Sometimes it could be a genuine requirement for an application to convert these alphanumeric phone numbers to numeric ones too. So, here's a User Defined Function (UDF) that does the job. The following code works only in SQL Server 2000. For previous versions like SQL Server 7.0, 6.5 etc., you need to convert this code, to a stored procedure with an output parameter.

The following scalar user defined function accepts a single parameter @pn and converts any characters in that parameter to their corresponding numbers and returns the converted phone number.


IF EXISTS
(
	SELECT 'Y'
	FROM INFORMATION_SCHEMA.ROUTINES
	WHERE		ROUTINE_NAME = 'ConvertPhoneNumber'
		AND	ROUTINE_SCHEMA = 'dbo'
		AND	ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
	DROP FUNCTION dbo.ConvertPhoneNumber
END
GO

CREATE FUNCTION dbo.ConvertPhoneNumber
(
	@pn nvarchar(26)
)
RETURNS nvarchar(26)
AS
BEGIN
	
	-- Copyright  2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To convert alpha numeric phone numbers to numeric
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 2000
	-- Date modified: 17th July 2002 22:50 GMT
	
	DECLARE @tmp nchar, @n char(26), @idx tinyint

	SET @n = '22233344455566677778889999'
	SET @idx = LEN(@pn)

	WHILE @idx > 0
	BEGIN
		SET @tmp = UPPER(SUBSTRING(@pn, @idx, 1))
		
		IF @tmp BETWEEN 'A' AND 'Z' 
		BEGIN
			SET @pn = STUFF(@pn, @idx, 1, SUBSTRING(@n, (ASCII(@tmp)-64), 1))
		END
		
		SET @idx = @idx - 1
	END
	
	RETURN @pn 
END


The following examples show the usage of this function:

Example 1:
SELECT dbo.ConvertPhoneNumber('1 800 Microsoft') AS 'Converted number'
GO


Converted number
--------------------------
1 800 642767638

(1 row(s) affected)

Example 2:
SELECT dbo.ConvertPhoneNumber('1 425 MS FAX') AS 'Converted FAX number'
GO

Converted FAX number
--------------------------
1 425 67 329

(1 row(s) affected)

Example 3:
SELECT dbo.ConvertPhoneNumber(Phone) AS 'Phone Number'
FROM dbo.Customers
GO

Example 4:
DECLARE @Phone varchar(20)
EXEC @Phone = ConvertPhonenumber '1 800 GO ALAMO'
SELECT @Phone AS 'Phone Number'
GO

Phone Number
--------------------
1 800 46 25266

(1 row(s) affected)

Btw, the above UDF can be used to decode SMS messages into a numeric representation.