Tag: Format

Categories
SQL

Format Phone Number – SQL Server Function

SELECT dbo.CMN_FmtPhone('1234567891011')
Output: (123) 456-7891 Ext: 011

SELECT dbo.CMN_FmtPhone('1234567820')
Output: (123) 456-7820


CREATE FUNCTION CMN_FmtPhone
(
	@PhoneNumber varchar(15)
)
RETURNS varchar (15)
AS
BEGIN
	Declare @returnValue as Varchar(15)

	SELECT @returnValue =  
			CASE 
			WHEN LEN(LTRIM(RTRIM(@PhoneNumber))) = 10 
				THEN '(' + SUBSTRING(@PhoneNumber, 1, 3) + ')' 
						+ ' ' + SUBSTRING(@PhoneNumber, 4, 3) 
						+ '-' + SUBSTRING(@PhoneNumber, 7, 4) 
			WHEN LEN(LTRIM(RTRIM(@PhoneNumber))) > 10 
				THEN '(' + SUBSTRING(@PhoneNumber, 1, 3) + ')' 
						+ ' ' + SUBSTRING(@PhoneNumber, 4, 3) + '-' 
						+ SUBSTRING(@PhoneNumber, 7, 4) 
						+ ' Ext: ' + SUBSTRING(@PhoneNumber, 11, 5) 
			END

	RETURN @returnValue;

END