/*

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