preg_replace in MySQL

[faust]

I need to compare phones. One value is in the db and the other is user input. I don't know the exact format. It can be 1(800)600-005 or 1-800-600-005 or 1800600005. I need to strip the non-numeric charaters from the string. I know how to do it in php:

Code: Select all

$phone = preg_replace('/\D/', '', $phone);


But what about the value in the db? Is there a way to do something like:

Code: Select all

SELECT id  FROM tableName
WHERE preg_replace('/\D/', '', phone) = '" . $phone . "';


[Nullsig]

Look at this function:
http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

[faust]

thanks for the reply. I found a solution. 2 custom functions:

Code: Select all

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION NumericOnly (val VARCHAR(255))
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END;


Source: http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/

Hope this helps someone else