Changing phone number format

[mhkatz]

I need to take data from three different sources and create a single table. The telephone number are formatted differently in the three original table. Some are (123) 456-7890, some are 123-456-7890, and some are 1234567890. Is there a simple way of converting the telephone number field to one shared format? Any one of the above three formats would be acceptable.

 

[btothap]

This is a really old thread, but it took some serious digging for me to figure this out, so i figured I'd post what I did.

I had 2 tables, one i'd created, storing customer information including phone number, one that was imported from our phone system via an app called callanalyst.

For the internal table I had used an imput mask to format the phone number with parenthesis and a dash (999) 999-9999. The data received from the application software was an unformatted string of numbers, 9999999999.

I was looking for something similar to regular expressions in perl to strip the formatting from the original table. Here's what I came up with.

Phone: Mid([phone_home],2,3)+Mid([phone_home],7,3)+Right([phone_home],4)

works great for comparing the two databases.

hth

-sA