CODEWITHSUNDEEP
phpmysqlregex
John
John

Reputation: 10156

Using MySQL REGEX to match repeating numbers in phone numbers

I had a previous question using preg_replace but can't seem to get this to work using MySQL REGEX function. Basically, I have a database of phone numbers. For example:

8452100000

I want to be able to dynamically search for phone numbers like so:

2XX512YYYY

Where X and Y could be any digit, but each represents repetition of the same digit. I tried this so far and it's not working. I can get it to work with PHP preg_match(), but not with MySQL REGEX(). Can you see what I'm doing wrong here?

SELECT * FROM numbers WHERE number REGEXP '^2[[:digit:]]\1{1}512[[:digit:]]\2{4}'

Upvotes: 0

Views: 868

Answers (1)

elixenide
elixenide

Reputation: 44831

\1 and \2 will not work because MySQL doesn't support back-references to subgroups in regular expressions. You need to be explicit about what can match, like this:

SELECT * FROM numbers WHERE number REGEXP '^2(00|11|22|33|44|55|66|77|88|99)512(0000|1111|2222|3333|4444|5555|6666|7777|8888|9999)'

This is clunky, but this is how it is until MySQL implements a Perl-compatible regular expressions (PCRE) engine, unless you want to install an extension.

Upvotes: 2

Related Questions