Format US Phone

Worst case scenario, for when you do not have the ability to do Regex replace.

SELECT dbo.FormatUSPhone('989.989.9898') AS Phone
	,dbo.FormatUSPhone('(989)989.9898') AS Phone2
	,dbo.FormatUSPhone('(989) 989-9898') AS Phone3
	,dbo.FormatUSPhone('(989) 989-989') AS Phone4
	,dbo.FormatUSPhone('989/989/9898') AS Phone5
	,dbo.FormatUSPhone('9899899898') AS Phone6

Phone                     Phone2                    Phone3                    Phone4                    Phone5                    Phone6
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
(989) 989-9898            (989) 989-9898            (989) 989-9898            989989989                 (989) 989-9898            (989) 989-9898

IF OBJECT_ID(N'dbo.FormatUSPhone') IS NOT NULL
	DROP FUNCTION dbo.FormatUSPhone
GO

CREATE FUNCTION dbo.FormatUSPhone (@string VARCHAR(250))
RETURNS VARCHAR(25)
AS
BEGIN
	DECLARE @Formatted VARCHAR(25)

	SET @string = ltrim(rtrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(@string, ')', ''), '(', ''), '.', ''), ' ', ''), '-', ''), '/', ''), '', '')))
	SET @Formatted = (
			CASE
				WHEN len(@string) <> '10'
					THEN @string
				WHEN len(@string) = '10'
					THEN '(' + SUBSTRING(@string, 1, 3) + ')' + ' ' + SUBSTRING(@string, 4, 3) + '-' + SUBSTRING(@string, 7, 4)
				WHEN @string = ''
					THEN ' '
				END
			)

	RETURN @Formatted
END
GO

I have modified the code from the source link below to account for incorrect/invalid phone numbers. The original code will display NULL if the phone number is not valid where my edit will show the character stripped original string.

Source: http://nice-tutorials.blogspot.com/2009/04/how-to-format-phone-number-in-ms-sql.html