How to format phone number to add dashes

Question

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

k_nataraj Old Hand Points: 361 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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.