Format Phone Number function

Question

Format Phone Number function

harsha.majety

SSCommitted

Points: 1537
More actions
December 6, 2010 at 11:17 pm

#232156

Comments posted to this topic are about the item Format Phone Number function

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jay Discount Valued Member Points: 61 More actions · Answer 1

This code seems like a very convoluted way to parse a 10 digit number and put parenthesis, spaces and dashes in the result.

Why not check for numeric and if true use the substr command to create the result?

Mad Myche SSCommitted Points: 1593 More actions · Answer 2

Kinda need to agree with Jay on this one. Even though it is functional, it is not something I would be using for my applications.

For my usage, I would probably have used a RegEx on the client side to strip out the non-numerics, and used a client side format function as well.

Phone numbers are a tricky item to format, as the ISO standard is poorly written IMHO; and, different areas of the world have different rules which need to be applied when dialing.

Director of Transmogrification Services

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

Someone recently posted a function like the one below that I have modified to validate US/Canadian format phone numbers. Sorry that I can't remember the OP to give proper credit. But this works well for me. Obviously, it would have to be modified to handle international formats.

/*

SELECT dbo.ufnFormatUSPhone('7044567890')-- Valid Number

SELECT dbo.ufnFormatUSPhone('17044567890')-- Valid Number without initial '1'

SELECT dbo.ufnFormatUSPhone('4567890')-- Valid Number without area code

SELECT dbo.ufnFormatUSPhone('24567890')-- Returns NULL

SELECT dbo.ufnFormatUSPhone('123456789099999')-- Returns NULL

SELECT dbo.ufnFormatUSPhone('abc')-- Returns NULL

*/

ALTER FUNCTION [dbo].[ufnFormatUSPhone]

(

@phonenumber VARCHAR(20)

)

RETURNS VARCHAR(20)

AS

BEGIN

/** Remove White Space and non-Integer(s) values **/

WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0

BEGIN

SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')

END

SET @phonenumber = LTRIM(RTRIM(@phonenumber))

/** Get the number of digits **/

DECLARE @NumChars INT

SET @NumChars = LEN(@phonenumber)

/** Check to see if this number has a leading value of '1' **/

IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'

BEGIN

SET @phonenumber = RIGHT(@phonenumber,10)

SET @NumChars = LEN(@phonenumber)

END

/** If valid number of digits proceed with formatting **/

IF @NumChars = 7 OR @NumChars = 10

BEGIN

SET @phonenumber = REVERSE(@phonenumber)

/** Format Phone Number **/

SET @phonenumber =

REVERSE(LEFT(@phonenumber,4)

+ '-'

+ SUBSTRING(@phonenumber,5,3)

+ COALESCE(' )'

+ NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))

END

ELSE

SET @phonenumber = NULL

RETURN @phonenumber

END

Bharat Panthee Default port Points: 1448 More actions · Answer 4

I think it is better to write CLR function for it,if you are using sql server 2005 or 2008

forsqlserver SSCoach Points: 18902 More actions · Answer 5

forsqlserver

SSCoach

Points: 18902

May 5, 2011 at 1:58 am

#1320243

The value of Out parameter is not coming.

Thanks

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 6

Bharat Panthee (12/8/2010)
I think it is better to write CLR function for it,if you are using sql server 2005 or 2008

This is why I don't allow people to write CLRs on my servers. This isn't rocket science nor a performance problem for SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sarath.tata SSCommitted Points: 1640 More actions · Answer 7

Nice work

Steven Willis (12/7/2010)
Someone recently posted a function like the one below that I have modified to validate US/Canadian format phone numbers. Sorry that I can't remember the OP to give proper credit. But this works well for me. Obviously, it would have to be modified to handle international formats.
/*
SELECT dbo.ufnFormatUSPhone('7044567890')-- Valid Number
SELECT dbo.ufnFormatUSPhone('17044567890')-- Valid Number without initial '1'
SELECT dbo.ufnFormatUSPhone('4567890')-- Valid Number without area code
SELECT dbo.ufnFormatUSPhone('24567890')-- Returns NULL
SELECT dbo.ufnFormatUSPhone('123456789099999')-- Returns NULL
SELECT dbo.ufnFormatUSPhone('abc')-- Returns NULL
*/
ALTER FUNCTION [dbo].[ufnFormatUSPhone]
(
@phonenumber VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
/** Remove White Space and non-Integer(s) values **/
WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0
BEGIN
SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')
END
SET @phonenumber = LTRIM(RTRIM(@phonenumber))
/** Get the number of digits **/
DECLARE @NumChars INT
SET @NumChars = LEN(@phonenumber)
/** Check to see if this number has a leading value of '1' **/
IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'
BEGIN
SET @phonenumber = RIGHT(@phonenumber,10)
SET @NumChars = LEN(@phonenumber)
END
/** If valid number of digits proceed with formatting **/
IF @NumChars = 7 OR @NumChars = 10
BEGIN
SET @phonenumber = REVERSE(@phonenumber)
/** Format Phone Number **/
SET @phonenumber =
REVERSE(LEFT(@phonenumber,4)
+ '-'
+ SUBSTRING(@phonenumber,5,3)
+ COALESCE(' )'
+ NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))
END
ELSE
SET @phonenumber = NULL
RETURN @phonenumber
END