Algorithm for finding a number by mask

Question

MySQL

I can't figure out how to organize a search by phone mask in mysql.

There is a table that stores mobile phone numbers (always 10 digits) - approximately 2 million records.
It is necessary to organize a search for these numbers using masks: the mask also always consists of 10 characters, in which:
1) the numbers 0..9 - mean that there is a number in this place
2) X - means that there can be any number
3) A, B,C,D are digits that are different from each other (A is not equal to B, B is not equal to C, ...).

For example, if the mask is 911-AAA-X-BBB, then the numbers fall under it :
911-000-0-111
911-000-0-222
911-000-0-333
... 0-000 911-111-0-222 etc. but the numbers don't fit
911-001-0-222 (all occurrences of the letter A must correspond to one digit)
911-111-0-111 (A must not be equal to B)

I tried to do it using regexp, but it seems that it is impossible to implement the logic that A!=B or that all occurrences of A have the same digit.

Answer 1

I did not figure out how to do it through one regular expression, but you can try the following:
1. Generate a common regular expression.
For 911-AAA-X-BBB it will look like 911-(000|111|222|...|999)-[0-9]-(000|111|222|...|999)
2. Do comparison of blocks A,B,C,D through substrings.
For 911-AAA-X-BBB this would be SUBSTRING(phone,5,3) != SUBSTRING(phone,11,3)

Answer 2

I have 2 options:
Option 1 . Store the phone numbers themselves "by digit" - in 10 fields of the table (D0, D1, ..., D9), 1 field for each digit of the number. Perform indexing on these fields.
Pluses: at search indexes will be used.
Cons: the size of the table grows, the time it takes to update data when inserting / replacing, etc.
Example for mask 911-AAA-X-BBB:

SELECT * FROM table WHERE D0=9 AND D1=1 AND D2=1 AND D3=D4 AND D4=D5 AND D7=D8 AND D8=D9

Option 2 . When entering a mask (in the main program), prepare in advance a list of ALL (not necessarily contained in the table) numbers that satisfy the mask, and use this list in WHERE.
Pros: when searching, the index will be used.
Cons: with a “weak” mask, there may be quite a lot of such numbers in the list, and the request text itself will turn out to be unimaginably large.
This method will be good when the mask is "strong", for example: 911-AAAAAA-X (about 100 options in total)
Example for the mask 911-AAA-X-BBB:

SELECT * FROM table WHERE phone IN (9110001111, 9110001222, 9110001333, ...)

Answer 3

regexr.com?331cn
^911-(\d)\1{2}-0-((?!\1)\d)\2\2$

Answer 4

Try using regular expressions . Namely, \d{3}-\d{3}-\d-\d{3}

Answer 5

911-(0{3}|1{3}|2{3}|3{3}|4{3}|5{3}|6{3}|7{3}|8{3}|9{ 3})-X-(0{3}|1{3}|2{3}|3{3}|4{3}|5{3}|6{3}|7{3}|8{3 }|9{3})
I would use this option, I don't know for more,
but at the expense of checking that AAA =! BBB, if only to tear it out of the number and compare them separately, but then I wonder if it makes sense to make this a base, I'm not special in this

Answer 6

Yes, I missed one moment - in the first option, you need to add the condition that A != B. With it, the request will look, for example, like this:

SELECT * FROM table WHERE D0=9 AND D1=1 AND D2=1 AND D3=D4 AND D4=D5 AND D7=D8 AND D8=D9 AND D3!=D7