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?

  • 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 May 2 '18 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 May 3 '18 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 May 3 '18 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 May 3 '18 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 May 3 '18 at 12:42
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
  • 1
    This was how I finally did it: SELECT CONCAT('+46','', RIGHT(REPLACE(REPLACE(REPLACE(number,'-', ''), ' ', ''),'+',''),9)) AS adjusted_number FROM phone; – Andreas May 18 '18 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 May 20 '18 at 15:52
  • 1
    @eckes yea it seemed weird to me too. If you are gonna do it, though, that's one way. – James May 20 '18 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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