Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Format Phone Number function Expand / Collapse
Author
Message
Posted Monday, December 6, 2010 11:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 11:46 AM
Points: 9, Visits: 437
Comments posted to this topic are about the item Format Phone Number function
Post #1031041
Posted Tuesday, December 7, 2010 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:21 AM
Points: 7, Visits: 29
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?
Post #1031146
Posted Tuesday, December 7, 2010 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 138, Visits: 457
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
Post #1031277
Posted Tuesday, December 7, 2010 9:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1031291
Posted Wednesday, December 8, 2010 9:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 3:48 PM
Points: 444, Visits: 162
I think it is better to write CLR function for it,if you are using sql server 2005 or 2008
Post #1031983
Posted Thursday, May 5, 2011 1:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:30 AM
Points: 1,202, Visits: 1,946
The value of Out parameter is not coming.

Thanks

Post #1103679
Posted Friday, November 23, 2012 4:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse