Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

722 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