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
419 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 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 26

Expert Comment

by:ushastry
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 142

Accepted Solution

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

Author Closing Comment

by:martyje
ID: 31511180
Thanks much
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now