October 26, 2010 at 10:55 pm
Comments posted to this topic are about the item UDF: Phone Format
October 27, 2010 at 11:11 am
This function as written may allow bad data to get formatted as what might appear as valid numbers. I liked the general idea but only wanted to allow 10 or 7-digit numbers (or 11-digits with a leading '1') to get thru the formatting filter. Otherwise, I have no idea if the number is valid so would prefer it return null in such a case. These conditions only apply to U.S. numbers.
CREATE FUNCTION udfFormatUSPhone
(
@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
May 18, 2016 at 7:11 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply