CODEWITHSUNDEEP
mysqlregex
Dennis Hackethal
Dennis Hackethal

Reputation: 14275

Mysql regexp to allow numbers and '+'

I have a mysql table with a column that has phone numbers in it - some aren't really phone numbers, which I want to find out by firing a single query that says something like "set all phone numbers to NULL where the phone number consists of other characters than 0-9 and '+'"

So far I have come up with the following, but do not know how to add tolerance for '+':

$query="UPDATE table SET phone='NULL' WHERE phone REGEXP ('[0-9]')!=1";
mysql_query($query);

Does anybody know how to tolerate '+' in that query? Also, the '+' needs to be at the beginning of the phone number.

Thank you!

Charles

Upvotes: 1

Views: 3385

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

First, use NOT REGEXP instead of !=1. To allow + at the start optionally, use ^\\+?. Since + is a special character in regular expressions, it must be escaped with a backslash (which must be escaped with another backslash). You then need one or more digits ([0-9]+), up to the end of the string $.

$query="UPDATE table SET phone='NULL' WHERE phone NOT REGEXP '^\+?[0-9]+$'";

This will match anything that doesn't begin optionally with +, followed by digits only. If you also need to permit hyphens and dots in the phone number, add them into the [] character class:

'^\\+?[0-9.-]+$'

Upvotes: 1

Related Questions