asked on

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

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

Guy Hengel [angelIII / a3]

you could replace all the characters that are "noise":

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

Open in new window

Leon Teale

what phone is it?

can you not 'edit' the contact or number?

martyje

ASKER

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

Thanks.

Umesh

You are missing table name .

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

Open in new window

martyje

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

martyje

ASKER

Thanks much