Remove all non-numeric characters from the field

Question

I have a result set from a web form that includes a phone number for each set. the format of this phone number does not apply (some of them are xxxxxxxxxx, some of them (xxx) are xxx-xxxx, and some are xxx-xxx-xxxx). This was short-sighted, and now I need to get the result based on the phone number (Filters displayed for viewing).

The best way to solve this problem is to reformat the values ​​in this field using an SQL query so that they lose any non-numeric values. I tried several functions that I found on similar issues, and none of them work (I use mysql workbench and get a "function does not exist" error). This is what I do once, and I'm looking for a query that I can run that will split all non-numeric values. I only need to run it once, because I check the phone numbers so that they are numerical only here.

Is there an SQL query that will do what I need? With PHP it will be easy

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

But I can not find a way to do this using SQL.

Answer 1

There is no built-in function that will perform this operation in MySQL.

One option is to create your own saved function (if you have sufficient privileges in 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 check this out before using the 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 

Return:

  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 lines can give us a break to review what we really want to achieve. If it were me, I would create a new column and store the existing value in it before I did the 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) ; 

Answer 2

It is not beautiful, 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 need to duplicate the concatenated IF function more times depending on the length of the field and the amount of formatting.

See Query in Action in SQLFiddle

Answer 3

This might work, but a little tedious:

Get a list of numbers from a table

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

At each value

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

Then execute the regex pattern and update this value to id

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

Answer 4

On MySQL 8. 0+ there is a new built-in function called REGEXP_REPLACE. A clean solution to this question would be:

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