That works fine with 10 digit numbers, but fails if the number does not include the area code.Try this:create function [dbo].[FormatPhone](@PhoneNum varchar(50))returns varchar(50)as-- FormatPhone-- blindman, 3/2004-- Formats a string of 7-10 numeric values as a phone number, with or without the area code.begin-- Test parameters-- declare @PhoneNum varchar(50)-- set @PhoneNum = '6142462473'declare @ReverseNum varchar(50)set @ReverseNum = Reverse(@PhoneNum)return reverse(left(@ReverseNum, 4) + '-' + substring(@ReverseNum, 5, 3) + coalesce(' )' + nullif(substring(@ReverseNum, 8, 3), '') + '(', ''))end
e4 d5 xd5 Nf6