How to match phone number prefix to country from phonenumber in SQL

user_3623641 0
Viewed 2364

I am trying to extract the country code prefix from a list of numbers, and match them to the region that they belong to. The data might look something like this:

| id | phone_number   |
|----|----------------|
| 1  | +27000000000   |
| 2  | +16840000000   |
| 3  | +10000000000   |
| 4  | +27000000000   |

The country codes here are:

  • American Samoa: +1684
  • United States and Caribbean: +1
  • South Africa: +27

And the desired result would be something this:

| country                     | count |
|-----------------------------|-------|
| South Africa                | 2     |
| American Samoa              | 1     |
| United States and Caribbean | 1     |

There are some difficulties because

  • country prefix codes vary from 1 to 4 numbers and even without the country prefix,
  • phone number length varies from place to place.
  • I do not have write access to this DB, so adding another column, while probably the best solution, will not work in this use case

This is my current solution:

SELECT 
CASE
    WHEN SUBSTRING(phone_number,1,5) = '+1684' THEN 'American Samoa'
    WHEN SUBSTRING(phone_number,1,5) = '+1264' THEN 'Anguilla'
    ...
    WHEN SUBSTRING(phone_number,1,5) = '+1599' THEN 'Saint Martin'
    WHEN SUBSTRING(phone_number,1,4) = '+355' THEN 'Albania'
    WHEN SUBSTRING(phone_number,1,4) = '+213' THEN 'Algeria'
    ...
    WHEN SUBSTRING(phone_number,1,4) = '+263' THEN 'Zimbabwe'
    WHEN SUBSTRING(phone_number,1,3) = '+93' THEN 'Afghanistan'
    WHEN SUBSTRING(phone_number,1,3) = '+54' THEN 'Argentina'
    ...
    WHEN SUBSTRING(phone_number,1,3) = '+58' THEN 'Venezuela'
    WHEN SUBSTRING(phone_number,1,3) = '+84' THEN 'Vietnam'
    WHEN SUBSTRING(phone_number,1,2) = '+1' THEN 'United States and Caribbean'
    WHEN SUBSTRING(phone_number,1,2) = '+7' THEN 'Kazakhstan, Russia'
    ELSE 'unknown'
END as country_name,
count(*)
FROM users
GROUP BY country_name
order by count desc

There are ~205 WHEN ... THEN cases. It seems to be very inefficient and times out. I assume this is because it runs the pattern matching on every row. This would need to scale to roughly 10s of millions of rows

Is there a more efficient way to do this?

I am using postgreSQL 9.6.16

libphonenumber phone-number postgresql sql
1 Answers

In spite of reading the whole table, an index could help here. In order to aggregate the data per country code, the DBMS must sort all rows by country code. Sorting is an expensive operation, especially on large data sets. If you had an index on the country codes, the DBMS would find the codes already pre-sorted in the index and could avoid the work of sorting the data.

You don't have the separate country code in a column, but each phone number starts with the code, so you could index the complete phone number:

create index idx on users (phone_number);

Then you must make it obvious to the DBMS that you are interested in the beginnings of the string, so it will consider using the index. Invoking a function like SUBSTRING on the phone number is likely to make the the DBMS blind to this. Use LIKE instead. According to the docs (https://www.postgresql.org/docs/9.3/indexes-types.html), indexes on strings can be used with LIKE 'something%':

WHEN phone_number LIKE '+1684%' THEN 'American Samoa'

There is no guarantee this will help, but it's worth a try I think. It depends on whether the optimizer sees the advantage of using the pre-sorted phone numbers from the index.

user_22707620
Related