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:


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


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}'




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


I did that and still doesnt work. This is the number Im trying to find using that regex 2445121111 and nothing is coming back
– John
9 hours ago
Contrell I also switched {1} in the first group to {2} and still doesnt work. The field its in is a bigint. Does that matter?
– John
9 hours ago
Please see my edit; the last count should be {3} – you’re double-counting the digits in that group (1 + 4 = 5, but you want 1 + 3 = 4).
The fact that it’s in a bigint shouldn’t matter, but that’s not the best way to store phone numbers. Also, if you want to tag someone in a comment, use @ + the display name, like @EdCottrell. (“Contrell” is a misspelling and doesn’t do anything to notify anyone that you have replied).
@John using to test Ed’s regex returns the number itself and the repeated integers. An array with 3 elements for the number 2445121111


Tags:, ,