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!
llputneyAsked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
Run the first update to strip any existing format, then run a second update to give it the new format. You could combine it all into one, but gets kind of hard to understand, even though it would save a pass through the table.

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

UPDATE myTable
SET PhoneColumn = '(' + SUBSTRING(PhoneColumn, 1, 3) + ')' + SUBSTRING(PhoneColumn, 4, 4) + '-' + SUBSTRING(PhoneColumn, 7, 4)
0
 
fauxDBACommented:
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.
0
 
llputneyAuthor Commented:
can you give me the code for the replace function and substring?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
llputneyAuthor Commented:
can you please give me the code to do this?
0
 
dbbishopCommented:
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)
0
 
llputneyAuthor Commented:
i have to reformat (replace) all phone numbers in the following format (xxx)xxx-xxxx, i wont be retrieving it
0
 
dbbishopCommented:
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.
0
 
llputneyAuthor Commented:
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
0
 
dbbishopConnect With a Mentor Commented:
Second update should be:

UPDATE myTable
SET PhoneColumn = '(' + SUBSTRING(PhoneColumn, 1, 3) + ')' + SUBSTRING(PhoneColumn, 4, 3) + '-' + SUBSTRING(PhoneColumn, 7, 4)
0
 
dbbishopCommented:
Sorry!
0
 
llputneyAuthor Commented:
Thank you!!! (thank goodness i did it on a practice table, so no worries, smile!)
0
 
dbbishopCommented:
ALWAYS, ALWAYS, ALWAYS test first.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.