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