I want to find all rows in a phone column that have any character other than a plus sign (+) or numeric digit. It can be any character like slash, space, bracket, alphabet or any other character. The column has phone numbers, but may rows has space, brackets and slashes. I want to see those. Like:
+1.888.888.888 +1 (888) 888 8888 +1.8888888888
But theses are correct:
+18888888888 +33888888
and should not show.
I tried:
SELECT `phone` FROM `listings` WHERE `phone` NOT IN (SELECT `phone` FROM `listings` WHERE `phone` REGEXP '^+d')
And
SELECT `phone` FROM `listings` WHERE `phone` NOT IN (SELECT `phone` FROM `listings` WHERE `phone` REGEXP '^+[0-9]')
Error: Error in query (1139): Got error 'nothing to repeat at offset 1' from regexp
Advertisement
Answer
You were on the track with the second one. But all the characters of a class have to be in the square brackets. To negate the class the caret has to go in there as well, as first character. Outside it matches the beginning of the string.
There’s no need to use IN here though. You can filter directly with a WHERE clause.
SELECT phone
FROM listings
WHERE phone REGEXP '[^0-9+]';