2

I have seen a few question on SO similar to what I require but nothing seems to fit the bill.

I am in the position where I need to deal with a call record and determine the country using the phone number. The number dialed can be any country for example:

44 7899455120 - UK

34 965791845 - Spain

355 788415235 - Albania

Obviously the world would be great if all calling codes were two digits but this is not the case. Currently I have a database full of countries and their relevant codes and in order to match I need to effectively take the first digit of the number ie 4 for the UK example and do a query of the database eg:

SELECT * from countries WHERE code LIKE '4%'

This may give me for example 20 results. So I loop again and do say

SELECT * from countries WHERE code LIKE '44%'

This may give me say one result, now I can determine it is UK. Some codes however like Albania are three digits and require more loops and database calls. This seems quite rudimentary and inefficient but as is I cannot think of another way to achieve this. I realise three calls to a database may not seem like much but if you have 1000 calls to deal with they soon add up.

Looking at the following question:

What regular expression will match valid international phone numbers?

There seems to be some great information on validating a number against country codes, but not so much on determining the country code from a number. Any advice or suggestions on a cleaner method would be much appreciated.

Spaces in the phone are shown for clarity

Community
  • 1
  • 1
The Humble Rat
  • 4,586
  • 6
  • 39
  • 73
  • It may not possible without having specific matching point. There are only 196 countries in the world, not a big deal... – vamsi Jan 14 '15 at 08:53
  • You should consider running a more generic lookup with services offered by 3rd party, or a lookup table containing CC+Length for a "best guess". You can find the specifications @ ITU (recommend http://www.itu.int/rec/T-REC-E.164-201011-I/en) Also, see for example how Jamaica differs from USA (http://www.wtng.info/wtng-1876-jm.html) or how Israel/Palestine or Satelite services share "prefix" (CC) – Andy Thompson Jan 14 '15 at 08:55
  • Can you provide what is the search parameter? I mean, the input you send to MySQL to fetch the data, because depending on that we can give you a better answer. – Robert W. Hunter Jan 14 '15 at 09:06
  • This URL question is talking about google library **libphonenumber** . Please see if thats helpful – Shirishkumar Bari Jan 14 '15 at 09:19

4 Answers4

4

There is no overlap ambiguity in the country codes. Meaning: the country code 11 is illegal because 1 is assigned to North America. Similarly, 20 is Egypt and there are no other country codes that start with 20. And the country codes that start with 21 are all 3 digits.

Since the is no overlap ambiguity, you can directly search for the country code in one query for the phone number 12125551212 like this:

select country
     , code
  from countrycodes
 where code in ('121', '12', '1')

Again, there are no country codes 121 or 12, so the only criteria that will match is the 1.

longneck
  • 11,938
  • 2
  • 36
  • 44
  • I would like to suggest an improvement based on @longneck answer. `SELECT country_name FROM countries WHERE phone_code = (SELECT LEFT(var_phoneno , 1)) OR phone_code = (SELECT LEFT(var_phoneno , 2)) OR phone_code = (SELECT LEFT(var_phoneno , 3))` – davykiash Mar 31 '21 at 11:46
4

A library exists that will parse a string of digits and reformat it to international standards (a number like 4402081231234 to '+44 20 8123 1234'). It will also return the Phone Number region, 'GB' or 'US' from a number, if there is the country code embedded in the number.

https://github.com/googlei18n/libphonenumber The original library is in Java, but there are also versions in Javascript, Python, Ruby and PHP, among others.

Alister Bulman
  • 34,482
  • 9
  • 71
  • 110
  • Never did accept this answer, but I use this library all the time now. – The Humble Rat Jan 12 '16 at 08:31
  • 2
    @TheHumbleRat I am trying to understand how this solved your problem. Reading the docs it looks, like you need to provide the `number` **and** the `country` to create the number proto object. Correct? Am I'm missing something here? – Steven M Aug 22 '16 at 06:49
  • 1
    @StevenM I was wondering the same thing. The country code passed in is a default country code that is used only if the provided number cannot be used to determine the country code. The sample no. cannot be parsed by the library. Unless you prefix with '+'. – andho Nov 09 '16 at 11:30
0

Assuming the phone will always look like that:

$phone = "355 788415235"; // Albania
$parts = explode(" ", $phone);
$code = $parts[0]; // First part separated by space, 355.

Then query by that directly. No regular expression needed.

If that's not the case, consider separating the country code from the number on the input level.

Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • @TheHumbleRat Then it isn't possible. Because both `12 34567` and `1 234567` are valid numbers. Without spaces, the two different numbers become the same one. Separate the two on the input level. – Madara's Ghost Jan 14 '15 at 09:09
  • 2
    No, those two numbers are not valid. There is no ambiguity in the standard. See http://en.m.wikipedia.org/wiki/List_of_country_calling_codes – longneck Jan 19 '15 at 18:05
0

On your system, every phone number has white space after country code so you can use it to determine country.

  1. Create a table which has all country codes. Lıke

    id | country | code

    1 | Turkey | 90

    2 | Spain | 34

(There is a table for you: http://erikastokes.com/mysql-help/country.sql.txt )

Than explode your phone number. Delimeter is white space " ".

$phoneNumber = "355 788415235";

$countryCode = explode(" ",$phoneNumber); // it divides phone number to two parts.
$countryCode = $countryCode[0]; // it returns 355. We write index 0 because country code is first part.
//Now you can call your country by country code.

$sqlQuery  ="SELECT country FROM yourTableName WHERE code = '$countryCode' ";

...

//it will works like a charm. Because i currently using this.
hakki
  • 6,181
  • 6
  • 62
  • 106