Format string as phone number

Posted on 2009-04-28
Medium Priority
Last Modified: 2012-05-06
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
Question by:weimha
LVL 14

Expert Comment

ID: 24252585
you can use sql

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

LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 24253056
=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.

Author Closing Comment

ID: 31575546
Worked great, thanks.

Expert Comment

ID: 24915094
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")), "(###) ###-####")

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question