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.
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: