SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Harsha Majety / Format Phone Number function / Latest Posts

RE: Format Phone Number function

Jeff Moden — Fri, 23 Nov 2012 16:23:58 GMT

[quote][b]Bharat Panthee (12/8/2010)[/b][hr]I think it is better to write CLR function for it,if you are using sql server 2005 or 2008[/quote]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.

RE: Format Phone Number function

forsqlserver — Thu, 05 May 2011 01:58:09 GMT

The value of Out parameter is not coming.

RE: Format Phone Number function

Bharat Panthee — Wed, 08 Dec 2010 09:28:30 GMT

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

RE: Format Phone Number function

Steven Willis — Tue, 07 Dec 2010 09:11:43 GMT

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.[code="plain"]/*SELECT dbo.ufnFormatUSPhone('7044567890') -- Valid NumberSELECT dbo.ufnFormatUSPhone('17044567890') -- Valid Number without initial '1'SELECT dbo.ufnFormatUSPhone('4567890') -- Valid Number without area codeSELECT dbo.ufnFormatUSPhone('24567890') -- Returns NULLSELECT dbo.ufnFormatUSPhone('123456789099999') -- Returns NULLSELECT dbo.ufnFormatUSPhone('abc') -- Returns NULL */ALTER FUNCTION [dbo].[ufnFormatUSPhone]( @phonenumber VARCHAR(20))RETURNS VARCHAR(20)ASBEGIN /** 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[/code]

RE: Format Phone Number function

Mad Myche — Tue, 07 Dec 2010 08:42:41 GMT

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.

RE: Format Phone Number function

Jay Discount — Tue, 07 Dec 2010 05:15:22 GMT

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?

Format Phone Number function

harsha.majety — Mon, 06 Dec 2010 23:17:03 GMT

Comments posted to this topic are about the item [B]Format Phone Number function[/B]