December 6, 2010 at 11:17 pm
Comments posted to this topic are about the item Format Phone Number function
December 7, 2010 at 5:15 am
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?
December 7, 2010 at 8:42 am
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.
December 7, 2010 at 9:11 am
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
December 8, 2010 at 9:28 am
I think it is better to write CLR function for it,if you are using sql server 2005 or 2008
May 5, 2011 at 1:58 am
The value of Out parameter is not coming.
Thanks
November 23, 2012 at 4:23 pm
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
Change is inevitable... Change for the better is not.
March 27, 2015 at 10:48 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply