5

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?

Improve this question
6
  • 2
    Better late than never, I'd focus first on 1) making sure new data is coming in clean, and 2) cleaning all my old data to conform to my needed standards. Developing a matching process to accommodate dirty data is not something I'd recommend
    – LowlyDBA - John M
    Commented May 2, 2018 at 14:24
  • You have shown three "non-standard" formats of presumably the same number. It looks like 0700112233 and 070-0112233 do not include the international prefix, but you've also said that the DB contains numbers from various countries. Question is, do you want numbers without an international code to match any country or just one specific country? In other words, assuming for the sake of the example the DB has Ukrainian numbers, if I'm querying the DB for +380700112233, should 0700112233 be returned as a match? Or should such numbers match a particular country (say, Sweden) only?
    – Andriy M
    Commented May 3, 2018 at 7:12
  • Thanks. In the table I also store country. So I could possible assume that the customer country is the same as the "home country" of the phone prefix. So in this case I could possible restrict it so the query could add "AND COUNTRY = 'SE'. I think that would be the best approach in this case.
    – Andreas
    Commented May 3, 2018 at 8:08
  • Ah, I see. So the problem is only how to match a number in the international format with a number in an arbitrary format when the two actually mean the same number, or in other words, how to look for +46700112233 and be able to find 0700112233 or 070-0112233 or 460700112233 or some other variation, is that right?
    – Andriy M
    Commented May 3, 2018 at 10:02
  • Correct. The system will try to match a user and find out that +46700112233 is actually 0700-112233 in the DB and match that.
    – Andreas
    Commented May 3, 2018 at 12:42

2 Answers 2

Reset to default
0

this can be a doozy. I'd start with using the replace function to get rid of all the ('(',')','-') junk. You can also add it in universally, although that will be harder. It would look something like this:

    select replace(replace(replace(p.phone,'-',''),'(',''),')','') 
    from people p

To make every number 10 digits, with zeroes on the left side if it is shorter, you could do it this way:

    select right( '0000000000' + replace(replace(replace(p.phone,'-',''),'(',''),')',''),10)
    from people p

As for country codes... ideally they would be in their own column, but you could add them back in afterwards with something like this:

    select '011' + right( '0000000000' + replace(replace(replace(p.phone,'-',''),'(',''),')',''),10)
    from people p
    where country = 'America'

the full thing would look like this (look into batching it if its a really big table):

    select * into people_bak from people

     update p
     set Phone = 
    '011' + right( '0000000000' + replace(replace(replace(p.phone,'-',''),'(',''),')',''),10)
    from people p
    where country = 'America'

    select PostalCode, * from people
Improve this answer
3
  • 1
    This was how I finally did it: SELECT CONCAT('+46','', RIGHT(REPLACE(REPLACE(REPLACE(number,'-', ''), ' ', ''),'+',''),9)) AS adjusted_number FROM phone;
    – Andreas
    Commented May 18, 2018 at 9:52
  • Uh, you can’t fill phone numbers with multiple zeros and 10 is not the maximum. (ITU is 15 but private plans can differ)
    – eckes
    Commented May 20, 2018 at 15:52
  • 1
    @eckes yea it seemed weird to me too. If you are gonna do it, though, that's one way.
    – James
    Commented May 20, 2018 at 15:56
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.