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:
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
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?