Match phone numbers efficiently

For a mobile application we are considering validating a user based on their mobile phone number.

In the app we will restrict the user input so that it will be stored in the following format:

• +46700112233

That would mean in this case a Swedish phone number (+46), remove the initial 0 and then keep the remaining 9 digits (mobile phone numbers here are 10 digits including the leading 0).

However, the data currently saved in database include a lot of rows and no validation was made upon input. Meaning that the numbers saved could be in many different formats including

-
0700112233

-
070-0112233

-
460700112233

and so on.

So the type of the column in the database would probably be varchar.

At this time I'm unable to actually update the numbers already saved in the database to one general format, using for instance libphonenumber. I would prefer to limit the number of actual queries to the database as well.

The database currently includes phone numbers for about 10+ countries all saved in different formats. The DBMS used is MySQL.

What would be an efficient way to match the numbers accurately without querying the database for any of the possible formats?

The right thing to do is to fix the data as it goes in. Then you have only digit strings in a 'canonical' format. This includes prefixing with the country code. (However, you may want to keep the leading '+' just so users looking at the database will know that the country code is there.)

You will not get reasonable performance if you must repeatedly fix the numbers on the SELECT side.

StackExchange Database Administrators Q#205568, answer score: 2