MySQL regex at runtime

SELECT telephone_number
FROM table
WHERE teleph开发者_运维技巧one_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.

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:

• Pattern Matching

You can also use a UDF (user defined function) to have a REGEX_REPLACE.

https://launchpad.net/mysql-udf-regexp