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

Question

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

Answer 1

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.

Answer 2

My quick solution:

load a table with country, country_code sorted by country_code numeric value descending

country, country_code
'Saint Martin', '+1599' 
'Grenada', '+1473'
...
'US and Canada', '+1' --Area codes above if wanted

then do a

set @phone_number='  +1 473 2811055  ';
set @phone_number=REPLACE(@phone_number, ' ', '');
select top(1) * from #country_codes where left(@phone_number,len(country_code))=country_code;

will return

Grenada +1473 <-- top(1)
USA + Canada    +1

Cheers

Answer 3

Considering your codes tables as

    id      Codes  region_id  Zone                                                                          zone_id  Region                    Min Digits  Max Digits  
------  ---------  ---------  ----------------------------------------------------------------------------  -------  ------------------------  ----------  ------------
   383         93          1  Afghanistan                                                                         2  Afghanistan               (NULL)      (NULL)      
   377       9375          1  Afghanistan - Mobile - Afghan Telecom                                               3  Afghanistan               (NULL)      (NULL)      
   381       9371          1  Afghanistan - Mobile - Awcc                                                         4  Afghanistan               (NULL)      (NULL)      
   382       9370          1  Afghanistan - Mobile - Awcc                                                         4  Afghanistan               (NULL)      (NULL)      
   371       9378          1  Afghanistan - Mobile - Etisalat                                                     5  Afghanistan               (NULL)      (NULL)      
   379       9373          1  Afghanistan - Mobile - Etisalat                                                     5  Afghanistan               (NULL)      (NULL)      
   372       9377          1  Afghanistan - Mobile - Mtn                                                          6  Afghanistan               (NULL)      (NULL)      
   373      93767          1  Afghanistan - Mobile - Mtn                                                          6  Afghanistan               (NULL)      (NULL)      
   374      93766          1  Afghanistan - Mobile - Mtn                                                          6  Afghanistan               (NULL)      (NULL)      
   375      93765          1  Afghanistan - Mobile - Mtn                                                          6  Afghanistan               (NULL)      (NULL)      
   376       9376          1  Afghanistan - Mobile - Mtn                                                          6  Afghanistan               (NULL)      (NULL)      
   370       9379          1  Afghanistan - Mobile - Roshan                                                       7  Afghanistan               (NULL)      (NULL)      
   380       9372          1  Afghanistan - Mobile - Roshan                                                       7  Afghanistan               (NULL)      (NULL)      
   378       9374          1  Afghanistan - Mobile - Salaam                                                       8  Afghanistan               (NULL)      (NULL)      
 27885        355          2  Albania - Amc Fixed                                                                10  Albania                   (NULL)      (NULL)      
 25022        213          3  Algeria                                                                            16  Algeria                   (NULL)      (NULL)      
 25020       2136          3  Algeria - Mobile - Mobilis                                                         17  Algeria                   (NULL)      (NULL)      
 25019       2137          3  Algeria - Mobile - Orascom                                                         18  Algeria                   (NULL)      (NULL)      
 25021       2135          3  Algeria - Mobile - Wataniya Telecom                                                19  Algeria                   (NULL)      (NULL)      

DELIMITER $$

USE `test`$$

DROP FUNCTION IF EXISTS `codes_az`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `codes_az`(phone VARCHAR(100) ) RETURNS VARCHAR(100) CHARSET utf8
BEGIN
   DECLARE codes_az VARCHAR(100);
    
    SET codes_az = (SELECT a.region -- or a.codes or a.zone
FROM codes a, codes b
WHERE LEFT(phone,LENGTH(b.codes))=a.codes 
ORDER BY b.codes DESC
LIMIT 1);
        
   RETURN codes_az;
   
   END$$

DELIMITER ;

now just execute function:

select codes_az(phone_number), count(*) from table group by 1;