Remove all non-numeric characters from a field

问题:

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.

回答1:

There isn't any "builtin" function that will do this operation in MySQL.

One option is to create your own stored function (if you have sufficient privileges on the database).

 DELIMITER $$

 DROP FUNCTION IF EXISTS `uf_only_digits`$$

 CREATE FUNCTION `uf_only_digits`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

And be sure to test this before you use it an UPDATE statement.

 SELECT t.foo
      , uf_only_digits(t.foo)
   FROM ( SELECT '' AS foo
          UNION ALL SELECT ' x'
          UNION ALL SELECT 'a1b2'
          UNION ALL SELECT '1-888-555-1212 ext 213'
          UNION ALL SELECT '1-800-FLOWERS'
        ) t

Returns:

 foo                     uf_only_digits(t.foo)  newlen  
 ----------------------  ---------------------  --------
                                                       0
  x                                                    0
 a1b2                    12                            2
 1-888-555-1212 ext 213  18885551212213               14
 1-800-FLOWERS           1800                          4

(The last two rows might give us pause to reconsider what we really want to achieve. If it were me, I would creating a new column, and saving the existing value in it, before I did an UPDATE.)

 -- new column same size as `phone` column
 ALTER TABLE mytable ADD COLUMN orig_phone VARCHAR(40) NULL 
   COMMENT 'original phone value, before update to all digits';

 UPDATE mytable t
    SET t.orig_phone = t.phone ;

 UPDATE mytable t
    SET t.phone = uf_only_digits(t.phone) ;

回答2:

It's not pretty, but...

UPDATE table
SET data =
 CONCAT(
    IF (SUBSTRING(data, 01, 1) REGEXP '[0-9]', SUBSTRING(data, 01, 1), '')
,   IF (SUBSTRING(data, 02, 1) REGEXP '[0-9]', SUBSTRING(data, 02, 1), '')
,   IF (SUBSTRING(data, 03, 1) REGEXP '[0-9]', SUBSTRING(data, 03, 1), '')
,   IF (SUBSTRING(data, 04, 1) REGEXP '[0-9]', SUBSTRING(data, 04, 1), '')
,   IF (SUBSTRING(data, 05, 1) REGEXP '[0-9]', SUBSTRING(data, 05, 1), '')
,   IF (SUBSTRING(data, 06, 1) REGEXP '[0-9]', SUBSTRING(data, 06, 1), '')
,   IF (SUBSTRING(data, 07, 1) REGEXP '[0-9]', SUBSTRING(data, 07, 1), '')
,   IF (SUBSTRING(data, 08, 1) REGEXP '[0-9]', SUBSTRING(data, 08, 1), '')
,   IF (SUBSTRING(data, 09, 1) REGEXP '[0-9]', SUBSTRING(data, 09, 1), '')
,   IF (SUBSTRING(data, 10, 1) REGEXP '[0-9]', SUBSTRING(data, 10, 1), '')
,   IF (SUBSTRING(data, 11, 1) REGEXP '[0-9]', SUBSTRING(data, 11, 1), '')
,   IF (SUBSTRING(data, 12, 1) REGEXP '[0-9]', SUBSTRING(data, 12, 1), '')
,   IF (SUBSTRING(data, 13, 1) REGEXP '[0-9]', SUBSTRING(data, 13, 1), '')
,   IF (SUBSTRING(data, 14, 1) REGEXP '[0-9]', SUBSTRING(data, 14, 1), '')
,   IF (SUBSTRING(data, 15, 1) REGEXP '[0-9]', SUBSTRING(data, 15, 1), '')
,   IF (SUBSTRING(data, 16, 1) REGEXP '[0-9]', SUBSTRING(data, 16, 1), '')
)
WHERE condition

You may have to duplicate the concatenated IF function more times depending on the length of field and how much formatting was entered.

See the query in action at SQLFiddle

回答3:

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"); 
} 

回答4:

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