InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 12:27:53 GMT20http://www.sqlservercentral.com/Forums/Topic1011261-2823-1.aspxThis 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.[code="sql"]CREATE FUNCTION udfFormatUSPhone( @phonenumber VARCHAR(20))RETURNS VARCHAR(20)ASBEGIN /** Remove White Space and non-Integer(s) values **/ WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) &gt; 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] Wed, 27 Oct 2010 11:11:23 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1011261-2823-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/User+Defined+Function/71413/">UDF: Phone Format</A>[/B]Tue, 26 Oct 2010 22:55:36 GMTsaul.g.perez