Searching a Phone Number in a MySQL Database Table with two or more phone field columns having phone number in different formats
I have a database table with 2 columns - phone and mobile.
Both columns have phone numbers in different formats like:
+(91) 950-232-9020
+91 950-232-9020
+91 9502329020
09502329020
From my PHP scriptm if I want to search phone number '9502329020',
I want it to return 4 rows.
So far I have found 1 solution using % after every digit as:
Select * from table
where phone like '%9%5%0%2%3%2%9%0%2%0' or
mobile like '%9%5%0%2%3%2%9%0%2%0';
But if I have to search millions of rows, I would need an optimized solution.
Can anyone help me out with an optimized solution, somewhat like using regex in a MYSQL query.
Note: While saving in the database, phone numbers are saved in user friendly formats.
I think you are using wrong way(since you except millions of rows). Whatever you do your bottle neck will be lack of index or you will be unable to use index if available also. You should have designed your column correctly, also I find your requirement ' While saving in database, phone numbers are to be saved in user friendly format.' absurd. Every coins has two sides, you cant consider only one side and ignore the other side. In your case it is easier to save and display data, but it is near impossible to search on the same data.
You will have to find a middle ground which will dictate you how to save the data, but will make searching faster. Since you already have a column (which you use for storing and displaying phone number), I would suggest add another column 'formattedphonenumbers' as varchar. it should store the same phone number but in proper format that can be used as index. In this column remove all characters that are not number and then reverse it.
Eg If phone number is +(91) 950-232-9020 then in formated column it should get stored as 020923205919(in reverse), now you will be able to use index properly. When searching eg.950-232-9020 or +(91) 950-232-9020 or 0950-232-9020, do the same thing strip all not number characters reverse it and search as where formatted number like '020923205919%' if country code was not given then also it will be able to search.
Regular expressions are your friends:
$number="9502329020";
$regexp = "(0|.* )?".substr($number,0,3)."-?".substr($number,3,3)."-?".substr($number,6,4);
$subquery = "SELECT * FROM table WHERE phone REGEXP '$regexp' or mobile REGEXP '$regexp'";
@Criesto : Just implemented as :
Creating **new table 'filtered_phonenumbers'**
with two columns 'recordid' and 'phone_number'
It will have multiple phone numbers for a single record,
saved after strip off all special characters like space,(,),+,-
Eg. I have record with
recordid = '1' and
phone = +(91) 950-232-9020
mobile = 9502329020
So made a new table 'filtered_phonenumbers'
where record is saved both both phone and mobile in filtered form as follows :
recordid | phone_number
1 | 919502329020
1 | 9502329020
Whenever a phone number is to be searched, it can be searched in this new table, then the record can be manipulated by recordid