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 06, 2010 11:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 11:16 AM
Points: 5, Visits: 303
Comments posted to this topic are about the item Format Phone Number function
Post #1031041
Posted Tuesday, December 07, 2010 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 31, 2011 6:48 AM
Points: 7, Visits: 26
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 07, 2010 8:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 18, 2012 1:25 PM
Points: 95, Visits: 331
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 07, 2010 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 16, Visits: 207
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 08, 2010 9:28 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 444, Visits: 155
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 05, 2011 1:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 1,129, Visits: 1,768
The value of Out parameter is not coming.

Thanks

Post #1103679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse