Determine Country from Telephone Numbers

Question

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

Solution 1

A library exists that will parse a string of digits and reformat it to international standards (a number like 4402081231234 to '[phone]'). 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.

Solution 2

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.

Solution 3

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.

Comments

[Madara's Ghost]

@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.

[Popeye The Sailor]

No, those two numbers are not valid. There is no ambiguity in the standard. See en.m.wikipedia.org/wiki/List_of_country_calling_codes

[The Humble Rat]

Never did accept this answer, but I use this library all the time now.

[Steven M]

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

[Leonid Mamchenkov]

The link is a 404 now.

[andho]

@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 '+'.

[davykiash]

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))

[irishman]

What if the phone number is 441615551212? it cannot be exploded using space and both 44 and 441 are prefixes.