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