?
Solved

Replace Phone Number Format

Posted on 2007-08-06
12
Medium Priority
?
2,708 Views
Last Modified: 2013-11-05
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!
0
Comment
Question by:llputney
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:fauxDBA
ID: 19640983
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
 

Author Comment

by:llputney
ID: 19640998
can you give me the code for the replace function and substring?
0
 

Author Comment

by:llputney
ID: 19641689
can you please give me the code to do this?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 15

Expert Comment

by:dbbishop
ID: 19641797
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
 

Author Comment

by:llputney
ID: 19641807
i have to reformat (replace) all phone numbers in the following format (xxx)xxx-xxxx, i wont be retrieving it
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19641809
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
 
LVL 15

Accepted Solution

by:
dbbishop earned 2000 total points
ID: 19641824
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
 

Author Comment

by:llputney
ID: 19641876
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
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 2000 total points
ID: 19641877
Second update should be:

UPDATE myTable
SET PhoneColumn = '(' + SUBSTRING(PhoneColumn, 1, 3) + ')' + SUBSTRING(PhoneColumn, 4, 3) + '-' + SUBSTRING(PhoneColumn, 7, 4)
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19641883
Sorry!
0
 

Author Comment

by:llputney
ID: 19641919
Thank you!!! (thank goodness i did it on a practice table, so no worries, smile!)
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19641930
ALWAYS, ALWAYS, ALWAYS test first.
Thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question