Search multiple phone number formats via PHP from MySQL

Question

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.

http://stackoverflow.com/questions/9099469/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record — Rahman Qaiser, Sep 04 '15 at 05:57
@ShivaniAggarwal it will be better if you create another column and add the phone number stripping out any special characters. — Criesto, Sep 04 '15 at 06:01
Can you put your table and some example data on www.sqlfiddle.com? — Raymond Nijland, Sep 04 '15 at 06:08
Take a look at this SO answers: http://stackoverflow.com/questions/5401714/search-phone-numbers-in-database-ignoring-special-characters — Criesto, Sep 04 '15 at 06:39

score 0 · Answer 1 · answered Sep 04 '15 at 06:20

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.

when using this i advise to use two columns one with the normal format and one with the reversed.. Or column reversed doesnt make much sense if your dont use or read sql column comments. — Raymond Nijland, Sep 04 '15 at 06:45
@Ratna - it will create problem in case of different country codes. — Shivani Aggarwal, Sep 04 '15 at 07:09
@Shivani Aggarwal Can you tell me how? you too can try with different country code. — Ratna, Sep 04 '15 at 07:47

score 0 · Answer 2 · answered Sep 04 '15 at 06:26

0

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'";

answered Sep 04 '15 at 06:26

Amarnasan

14,939
5
33
37

1

They are still your friends. Slow friends. – Amarnasan Sep 04 '15 at 06:28
What do you mean "Its a complicated one"? Complicated to implement? Complicated to understand? Complicated to make it work because it is too slow? – Amarnasan Sep 04 '15 at 07:12
@Amarnasan - No doubt it worked but will take a lot of time. As I have 3 modules with 3-4 phone fields in each and many records in each module. Incoming number is also of not same type. So it will take time to match a single phone number , need loops. So at present , I made a cron job which will run behind the scene , and remove special characters from phone number and save number in a separate table along with unique record Id. So I have to search only in a single table without loops. – Shivani Aggarwal Sep 04 '15 at 09:25

Shivani Aggarwal · Answer 3 · 2015-09-04T07:14:40.283

@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

Search multiple phone number formats via PHP from MySQL

3 Answers3