Formatting Phone Numbers

[Dale]

Hello everyone!

My phone numbers are 9999999999 I want to ENTER dashes in the correct
spots. 999-999-9999

In some cases, the data entry people made mistakes and entered 99-9999-9999
or 999-9999999

What query would I use to format my numbers in all cases like I need them?

Dale

 

[Chaim]

Maybe it would be easier to remove all of the dashes from each phone number
and then format them on the fly as you display them?

It seems to be much easier to do an update and convert all of the phone
numbers containing dashes to a standard dash-less form using:
UPDATE [Your Phone Number Table]
SET [Phone Number] = Replace([Phone Number], "-", "");

Then when you select and display them, use:

SELECT FORMAT ([Phone Number], "###-###-#####")
FROM [Your Phone Number Table] etc.

(Not guaranteeing that the FORMAT expression is correct)

I think this is easier because the REPLACE will change all of the '-' to ''
whenever it encounters one. You don't have to worry about all of the
different possibilities.

Good Luck!