Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

preg_replace in MySQL

Codes here !

Moderators: macek, egami, gesf

preg_replace in MySQL

Postby faust » Tue Jun 05, 2012 3:13 am

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 . "';
faust
New php-forum User
New php-forum User
 
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: preg_replace in MySQL

Postby Nullsig » Tue Jun 05, 2012 5:37 am

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: preg_replace in MySQL

Postby faust » Tue Jun 05, 2012 6:20 am

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 :)
faust
New php-forum User
New php-forum User
 
Posts: 109
Joined: Thu May 03, 2012 7:22 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron