Search code examples
mysqlsqlregexphone-number

REGEX for NOT matching phone number

I'm trying to match anything that doesn't match my phone number format of ###-###-####.

I have this to find a string of 10 digits

SELECT id, cust_num FROM `leads` WHERE cust_num REGEXP '[0-9]{10}'

This just finds anything that is 10 digits long. Thats been good so far for finding entries where someone just typed a string of digits as a number,

However I want to find anything that doesnt match this format.

 555-555-5555

So I want to find any and all that don't match the above format

 /* For example, it should find all these*/
 5555555555
 555 555 5555
 (555)5555555
 555-555-555
 (555)-555-5555

What is the correct REGEX to find all that does not match ###-###-####?

Solution 
  • SELECT id, cust_num FROM `leads` WHERE cust_num NOT REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}'

    Should do it.