mySQL regex in the where clause

Question

SELECT telephone_number
FROM table
WHERE telephone_number REGEXP '^1[() -]*999[() -]*999[() -]*9999$';

how do i make so its valid for any number format and any number like

407-888-0909
1(408)998-7654
7776654433
876-7788

right now its only valid for 1-999-999-9999

Answer 1

Use:

SELECT telephone_number
  FROM table
 WHERE telephone_number REGEXP '^1[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$';

Reference:

• Pattern Matching