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

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:

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