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
424 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 143

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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