如何准确验证数据库中的电话号码？ [关闭]

Question

We are building an app that needs to check which contacts in your address book are already members of our app. We have a MySQL table with user information. This table has the phone numbers of all existing members with country code.

We could easily perform a MySQL IN statement to match all phone numbers with our database but, the issue lies in how people store contacts in their address book. Some people store phone numbers with country codes and some without. How do we match these correctly?

We could also match with usernames but, our needs specifically is to search by phone number.

Answer 1

The national conventions for writing telephone numbers vary by country.

And hence, it would be a better option to include country, along with phone number in search criteria.

And if you are storing phone numbers in country specific convention format like:

+--------+-----------+--------------------+----------------------------------------------------+
| India  | Land Line | +cc-(0)AAA-BBBBBBB | AAA - STD, and BBBBBBB - phone number              |
| India  | Mobile    | +cc-AAAAA-BBBBB    | AAAAA - STD, and BBBBB - phone number              |
| India  | Toll Free | 1-800-123456       | 10 digit number can be called from anywhere        |
+--------+-----------+--------------------+----------------------------------------------------+
| USA    | General   | (NPA) NXX-XXXX     | NPA - area code, and NXX-XXXX - subsccriber number |
+--------+-----------+--------------------+----------------------------------------------------+

then, you can't just search by phone number.

It would be more convenient to search country specific numbers.

If you still want to search by phone number only, you may need to remove the country specific formats and then perform a comparison on phone numbers only for either partial or full match. For matched numbers you may require to return both country code or name and phone number.