Format string as phone number

I have a stored procedure that returns a a string as 1234567890.  I use this stored procedue in my SQL Server Report.  I want to display it is the format as (123) 456-7890.  I've tried =Format(First(Fields!CONTACTNUMBER.Value, "Phone"), "(###) ###-####") but it displays as (###) ###-#### instead of (123) 456-7890.

How can I do this in the SQL Report?  We're using SQL 2008
weimhaAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
Use
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
It will also work if you have dashes - or dots . in there.
0
 
EmesCommented:
you can use sql

  Declare @phone varchar(10)
  set @Phone = '123456789'
select '('+substring(@phone,1,3)+') ' + substring(@phone,4,3)+'-'+substring(@phone,6,4)

0
 
weimhaAuthor Commented:
Worked great, thanks.
0
 
bhoenigCommented:
The reason you are getting the (###) ###-#### is because the FORMAT function is seeing the CONTACTNUMBER.Value as string.  If you convert it to a double then it will work as you originally intended.  Use the CDBL() function.

=Format(CDBL(First(Fields!CONTACTNUMBER.Value, "Phone")), "(###) ###-####")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.