MySQL regex at runtime

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

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 ...

Answer 2

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

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

Answer 3

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.

Answer 4

Use:

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

Reference:

• Pattern Matching