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
Here is a simple MySQL regex that allows certain characters between groupings of numbers.
SELECT telephone_number
FROM table
WHERE telephone_number REGEXP '^1[() -]*999[() -]*999[() -]*9999$';
This matches your records but does not format them such that the misc. characters are removed, but you at least can find the records that match the number in question.
You could easily fix the formatting by changing this into an update statement.
replace or alter the data in code and update the database. Regexp only gives you a boolean result whether the pattern was matched or not.MySQL doesn't have a regular expression replace function, but if you have a limited number of unwanted characters you could use a series of replace statements, eg.
select replace(replace(replace(telephone, '-', ''), '(', ''), ')', '') from ...
Use:
SELECT telephone_number
FROM table
WHERE telephone_number REGEXP '^1[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$';
Reference:
You can also use a UDF (user defined function) to have a REGEX_REPLACE.
