Remove all non-numeric characters from a field

Question

I have a results set from a webform that includes a phone number for each set. the format of this phone number is not enforced (some are xxxxxxxxxx, some are (xxx)xxx-xxxx and some are xxx-xxx-xxxx). It was short sighted, and now I need to be able get a result based on the phone number (Views exposed filter).

The best way for me to solve this is to reformat the values in this field with an sql query so that they are stripped of any non-numeric values. I've tried a couple of functions I've found on similar questions, and none seem to be working (I'm using mysql workbench and getting a "function does not exist" error). This is something I'm doing once and am looking for a query I can run that will strip all non-numeric values. I'll only need to run it once, because I am validating phone numbers to be numeric only from here on.

Is there a sql query that will do what I need? With PHP it would simply be

update table set data = preg_replace("/[^0-9]/", "", data) where condition

But I can't seem to find a way to do this with SQL.

Answer 1

On MySQL 8.0+ there is a new native function called REGEXP_REPLACE. A clean solution to this question would be:

update table set data = REGEXP_REPLACE(data, '[^0-9]+', "") where condition

Answer 2

This may work but little tedious:

Get the list of number from the table

$result = mysql_query("Select ID, number from Table"); 

On each value

while ($row = mysql_fetch_array($result)) { 
$ID = $row["ID"]; 
$NUM = $row["number"]; 

Then do a regex pattern and update that value to the ID

$NUM = eregi_replace("whateverregex","",$NUM); 

$sql = mysql_query("Update Table set number = $NUM where ID = $ID"); 
}