?
Solved

MySQL script to strip out non numeric

Posted on 2011-09-14
4
Medium Priority
?
421 Views
Last Modified: 2012-05-12
Hi,

I have a column with telephone numbers where some fools have put in text and () insdie here. I need a script which I can run that will strip all this rubbish out. I will then need to put this script in a cron job...

Thanks
0
Comment
Question by:andybrooke
  • 3
4 Comments
 
LVL 17

Expert Comment

by:Swadhin Ray
ID: 36536313
0
 
LVL 17

Expert Comment

by:Swadhin Ray
ID: 36536317
let us know if you need any more clarification
0
 

Author Comment

by:andybrooke
ID: 36536748
Will this clean the data? OR just select the data?
0
 
LVL 17

Accepted Solution

by:
Swadhin Ray earned 2000 total points
ID: 36537522
No cleanup just select and return .
instead of passing a sting you can pass the column name but if you want then you can use replacing too like :

## Selecting data

SELECT select uExtractNumberFromString(columnname) FROM table_name WHERE condition;

## For replacing
UPDATE table_name
SET columname = uExtractNumberFromString(columnname);

for updating entire table and for updating columns based on condition then

UPDATE table_name
SET columname = uExtractNumberFromString(columnname)
WHERE condition;
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…

589 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