Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I am trying to clean some phone number that contain special characters such as ( ) - and any extra spaces in phone number fields.

Posted on 2008-10-29
7
Medium Priority
?
442 Views
Last Modified: 2012-06-27
I am trying to clean some phone number that contain special characters such as ( ) - and any extra spaces in phone number fields. How do I take out just digits from (548) 552-5254.
I am trying to get 5485525224.

Any ideas?
Thanks
0
Comment
Question by:martyje
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22831347
you could replace all the characters that are "noise":


REPLACE(REPLACE(REPLACE(REPLACE(yourfield, ' ', ''), '-', ''), '(', ''), ')', '')

Open in new window

0
 
LVL 6

Expert Comment

by:Leon Teale
ID: 22831353
what phone is it?

can you not 'edit' the contact or number?
0
 

Author Comment

by:martyje
ID: 22831690
angelll:
Here's the query that I tried. What am I doing wrong here?
SELECT REPLACE( REPLACE( REPLACE( REPLACE(  `phone` ,  ' ',  '' ) ,  '-',  '' ) ,  '(',  '' ) ,  ')',  '' )

Thanks.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 26

Expert Comment

by:Umesh
ID: 22831767
You are missing table name .



SELECT REPLACE(REPLACE(REPLACE(REPLACE(`phone` ,' ','' ) ,'-',''),'(','' ),')','') FROM tablename;
 
It seems `phone` is a column name..

Open in new window

0
 

Author Comment

by:martyje
ID: 22831830
Sorry, should have asked the question differently. I want to update that change in the database permanently. All the phone numbers that I have in `phone` column, I want to take out all the special characters and spaces.
Thanks.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22831873
you will need to UPDATE, though:
0
 

Author Closing Comment

by:martyje
ID: 31511180
Thanks much
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

927 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