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

Answers


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.-]+$'

Need Your Help

Create a String with a single char with dynamic range

java jasper-reports ireport

i am making a report using JasperReports, which is connecting to a database table that has an integer field "LEVEL".

How do I test php pages on XAMPP without typing "localhost/mysites/practice.php" and just clicking on php/html icon?

php xampp localhost double-click

How do I do this? It's not much of a hassle but at times it would be quite convenient if I could just double click on the php/html file to test my php scrips on my localhost/XAMPP server instead of...