asked on

Replace Phone Number Format

I have a phone number column in my SQL database that have varying formats such as
xxx-xxx-xxxx, (xxx) xxx-xxxx, (xxx) xxx xxxx, etc

I need an update query where I can reformat all phone numbers to look like this:

(xxx)xxx-xxxxx

Thanks in advance!

fauxDBA

What I would suggest is create another column. Let say ColTry

Use the below query to copy all the telephone nos. to this column

update table
set ColTry = TelephoneNoColumn

Next using the replace function replace all the symbols like (, ), -, to '' (i.e. blank), Thus this new column would only have nos. e.g.

If you had 8668510001,866-851-0001,(866)8510001, everything would be 8668510001

Next using Substring & split the no & concatinate it with the ( ).

Regards
Bharat Butani.

llputney

ASKER

can you give me the code for the replace function and substring?

llputney

ASKER

can you please give me the code to do this?

D B

To save it to the database:
PhoneColumn = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PhoneNbr, '(', ''), ')', ''), ' ', ''), '-', ''), '.', '')

To retrieve it:
PhoneNumber = '(' + SUBSTRING(PhoneColumn, 1, 3) + ')' + SUBSTRING(PhoneColumn, 4, 4) + '-' + SUBSTRING(PhoneColumn, 7, 4)

llputney

ASKER

i have to reformat (replace) all phone numbers in the following format (xxx)xxx-xxxx, i wont be retrieving it

D B

You can standardize all phone numbers currently in the database by running:

UPDATE myTable
SET PhoneColumn = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PhoneColumn, '(', ''), ')', ''), ' ', ''), '-', ''), '.', '')

That will remove all existing formatting from the current phone numbers in the table.

I used @PhoneNbr in the first example, assuming you are passing the phone number in a stored procedure.

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

llputney

ASKER

I used this code for the update:
UPDATE myTable
SET PhoneColumn = '(' + SUBSTRING(PhoneColumn, 1, 3) + ')' + SUBSTRING(PhoneColumn, 4, 4) + '-' + SUBSTRING(PhoneColumn, 7, 4)

and the results brought back 11 numbers like this (xxx)xxxx-xxxx

it should only be 10 numbers like (xxx)xxx-xxxx

SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

Sorry!

llputney

ASKER

Thank you!!! (thank goodness i did it on a practice table, so no worries, smile!)

D B

ALWAYS, ALWAYS, ALWAYS test first.
Thanks.