How to format phone number to add dashes

• chris.gidley

  SSC Enthusiast

  Points: 180

  More actions

  December 22, 2011 at 9:24 am

  #252395

  Greetings,

  This seems like it should be a lot easier than it is ending up for me, but maybe I am missing something obvious. I have a field on a report that pulls in a phone number with no formatting, and I would like to apply a mask/format so that it shows as ###-###-####.

  I am looking at the properties for the given text box, under number section and select Custom formatting option. I have tried entering various renditions of xxx-xxx-xxxx into this box, but nothing will add any sort of formatting to my phone numbers.

  What do I need to enter into the custom formatting box to get it to display with dashes?

  Thank you.

• k_nataraj

  Old Hand

  Points: 361

  More actions

  December 22, 2011 at 12:34 pm

  #1424930

  First thing I can think of is, data type of ur field. Try converting to various types, i would start with int and apply the masking. If nothing works then use sub string or left / right functions along with concatenation to achieve ur outcome

  Goodluck

  Natraj

• Jaselnewpar

  SSC Eights!

  Points: 979

  More actions

  December 22, 2011 at 1:34 pm

  #1424945

  As long as the format is static:

  SELECT LEFT(PhoneNumber, 3) + '-' + SUBSTRING(PhoneNumber, 3, 3) + '-' + RIGHT(PhoneNumber, 4)

• chris.gidley

  SSC Enthusiast

  Points: 180

  More actions

  December 22, 2011 at 2:47 pm

  #1424969

  Thanks for the input guys. I was hoping to be able to use a mask expression (like i've seen in many threads for data formatting, currency, etc.) something like ###-###-#### but it appears that is not possible, so instead i used the LEFT RIGHT approach and did a case statement for each phone number field, something like this:

  (CASE WHEN PhoneNumber not like '%-%' and PhoneNumber Not like '' THEN LEFT(PhoneNumber, 3) + '-' + SUBSTRING(PhoneNumber, 3, 3) + '-' + RIGHT(PhoneNumber, 4) ELSE PhoneNumber END) AS LocalPhone

  It just feels like overkill, but maybe that is my best option. Thanks for the input, again.