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

Asked 21/3, 2020 at 9:6 Answered 25/10, 2023 at 12:8

sql postgresql phone-number libphonenumber

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

Denude answered 21/3, 2020 at 9:6 Comment(9)

Why you don't make a separate column for code prefix – Differential 21/3, 2020 at 9:9

This is not a task well suited to be done in a database; instead you should bring in the country code from your original data source. Side note: US numbers do not look like +1000000000; a valid US number consists of 10 digits, e.g. +12128675309. – Trull 21/3, 2020 at 9:10

@Differential I don't have a way to make db mimgrations at present, I only have read access. Will update the question. – Denude 21/3, 2020 at 9:10

Good point @TimBiegeleisen - I guess I just wanted to highlight the prefix, not add in a potentially real number 😬 – Denude 21/3, 2020 at 9:12

Do you know for sure that a valid U.S. phone number does not start with +1684 ? – Seriema 21/3, 2020 at 9:16

@Seriema In fact, 684 is the area code assigned to American Samoa. But, such a valid US Samoan number would only have 7 digits after the area code, e.g. +16841234567. At the very least, the total length would also have to be checked, and maybe that wouldn't be enough in some cases. – Trull 21/3, 2020 at 9:20

libphonenumer does return a field Phone Number region, (see: libphonenumber.appspot.com/… ), and also a field isValidNumber – Lallans 21/3, 2020 at 10:32

You might be better off doing the work in a computer language and just select * from users for the database access. – Bausch 21/3, 2020 at 11:20

I don't see why a case expression with 200 parts would generate a time-out error. I think something else might be going on. Does it timeout with 100 parts? 50? 20? – Waist 21/3, 2020 at 14:2

Gladly sharing my own solution

This is a Function that parse all possible dial codes. The source for them is Wikipedia (from 2020).

This function is build for ORACLE. But the big CASE and SUBSTR() are standard SQL so it should be extremely easy to adapt this for postgress or any other RDBMS if necessary.

It takes in input a full phone number with dial code including the leading "+", and no spaces or any special chars. If your numbers are in another format you will have to add a bit of logic at the beginning of the function to refactor them.

It returns in output the country code of that phone number in ISO code 2-Chars.

If you wonder why I broke the Case in sub-Cases for each continent, it is only to branch faster. This function is the best approach you can have from a performance point-of-view, because it doesnt require any jointure to a dial code table.

Running on a BIG international production environment for 3 years and no issues so far. BIG = billions rows and dozen millions of unique phone numbers. I also checked the results from a random set of 100k numbers returned by my function, against the google/libphonenumber results for the same numbers, and I had 100% of identical results.

You can thus blindly trust this.

create or replace FUNCTION FU_PhoneCountry (piv_phonenumber IN VARCHAR2 DEFAULT '') RETURN CHAR
IS

-- This function takes a phone number in input and send back the COUNTRY 2-Char ISO code from the dial code of that phone number.
-- Source for the dial codes is wikipedia.
-- Expected phone number input is international format beginning with +, ie : +32478976543

l_phonenumber  VARCHAR2(50);

BEGIN


    IF piv_phonenumber IS NULL OR piv_phonenumber='' THEN
        -- Enpty number, stop.
        DBMS_OUTPUT.put_line('No input phone number');
        RETURN '';
    END IF;


l_phonenumber := RTRIM(piv_phonenumber);


    IF SUBSTR(l_phonenumber,1,1)<> '+'  THEN
        -- wrong  number, stop.
    DBMS_OUTPUT.put_line('Phone number shpuld begin with + !!!! ' || SQLCODE || chr(13) || SQLERRM) ;
    RETURN '';
    END IF;


    RETURN

(   CASE

        /* ================ NORTH AMERICA =============== */

        WHEN SUBSTR(l_phonenumber,1,2)='+1' THEN
        CASE

            /* ----------------- CANADA -------------------*/
            /*  Alberta  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1403' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1587' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1780' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1825' THEN 'CA'
            /*  British Columbia  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1236' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1250' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1604' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1672' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1778' THEN 'CA'
            /*  Manitoba  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1204' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1431' THEN 'CA'
            /*  New Brunswick  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1506' THEN 'CA'
            /*  Newfoundland and Labrador  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1709' THEN 'CA'
            /*  Nova Scotia and Prince Edward Island  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1782' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1902' THEN 'CA'
            /*  Ontario  */
            WHEN SUBSTR(l_phonenumber,1,5)='+1226' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1249' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1289' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1343' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1365' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1416' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1437' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1519' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1548' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1613' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1647' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1705' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1807' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1905' THEN 'CA'
            /*  Quebec */
            WHEN SUBSTR(l_phonenumber,1,5)='+1367' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1418' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1438' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1450' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1514' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1579' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1581' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1819' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1873' THEN 'CA'
            /*  Saskatchewan */
            WHEN SUBSTR(l_phonenumber,1,5)='+1306' THEN 'CA'
            WHEN SUBSTR(l_phonenumber,1,5)='+1639' THEN 'CA'
            /*  Yukon, Northwest Territories and Nunavut */
            WHEN SUBSTR(l_phonenumber,1,5)='+1867' THEN 'CA'


        /* ----------------- ISLANDS -------------------*/
                WHEN SUBSTR(l_phonenumber,1,5)='+1242' THEN 'BS'
                WHEN SUBSTR(l_phonenumber,1,5)='+1246' THEN 'BB'
                WHEN SUBSTR(l_phonenumber,1,5)='+1264' THEN 'AI'
                WHEN SUBSTR(l_phonenumber,1,5)='+1268' THEN 'AG'
                WHEN SUBSTR(l_phonenumber,1,5)='+1284' THEN 'VG'
                WHEN SUBSTR(l_phonenumber,1,5)='+1340' THEN 'VI'
                WHEN SUBSTR(l_phonenumber,1,5)='+1345' THEN 'KY'
                WHEN SUBSTR(l_phonenumber,1,5)='+1441' THEN 'BM'
                WHEN SUBSTR(l_phonenumber,1,5)='+1473' THEN 'GD'
                WHEN SUBSTR(l_phonenumber,1,5)='+1649' THEN 'TC'
                WHEN SUBSTR(l_phonenumber,1,5)='+1658' THEN 'JM'
                WHEN SUBSTR(l_phonenumber,1,5)='+1664' THEN 'MS'
                WHEN SUBSTR(l_phonenumber,1,5)='+1670' THEN 'MP'
                WHEN SUBSTR(l_phonenumber,1,5)='+1671' THEN 'GU'
                WHEN SUBSTR(l_phonenumber,1,5)='+1684' THEN 'AS'
                WHEN SUBSTR(l_phonenumber,1,5)='+1721' THEN 'SX'
                WHEN SUBSTR(l_phonenumber,1,5)='+1758' THEN 'LC'
                WHEN SUBSTR(l_phonenumber,1,5)='+1767' THEN 'DM'
                WHEN SUBSTR(l_phonenumber,1,5)='+1784' THEN 'VC'
                WHEN SUBSTR(l_phonenumber,1,5)='+1787' THEN 'PR'
                WHEN SUBSTR(l_phonenumber,1,5)='+1809' THEN 'DO'
                WHEN SUBSTR(l_phonenumber,1,5)='+1829' THEN 'DO'
                WHEN SUBSTR(l_phonenumber,1,5)='+1849' THEN 'DO'
                WHEN SUBSTR(l_phonenumber,1,5)='+1868' THEN 'TT'
                WHEN SUBSTR(l_phonenumber,1,5)='+1869' THEN 'KN'
                WHEN SUBSTR(l_phonenumber,1,5)='+1876' THEN 'JM'
                WHEN SUBSTR(l_phonenumber,1,5)='+1939' THEN 'PR'

                /* ------------------- USA ---------------------*/
                ELSE 'US'
        END

        /* =================== AFRICA ================== */

        WHEN SUBSTR(l_phonenumber,1,2)='+2' THEN
        CASE

            WHEN SUBSTR(l_phonenumber,1,3)='+20' THEN 'EG'

            WHEN SUBSTR(l_phonenumber,1,4)='+210' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+211' THEN 'SS'
            WHEN SUBSTR(l_phonenumber,1,4)='+212' THEN 'MA'
            WHEN SUBSTR(l_phonenumber,1,4)='+213' THEN 'DZ'
            WHEN SUBSTR(l_phonenumber,1,4)='+214' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+215' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+216' THEN 'TN'
            WHEN SUBSTR(l_phonenumber,1,4)='+217' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+218' THEN 'LY'
            WHEN SUBSTR(l_phonenumber,1,4)='+219' THEN ''

            WHEN SUBSTR(l_phonenumber,1,4)='+220' THEN 'GM'
            WHEN SUBSTR(l_phonenumber,1,4)='+221' THEN 'SN'
            WHEN SUBSTR(l_phonenumber,1,4)='+222' THEN 'MR'
            WHEN SUBSTR(l_phonenumber,1,4)='+223' THEN 'ML'
            WHEN SUBSTR(l_phonenumber,1,4)='+224' THEN 'GN'
            WHEN SUBSTR(l_phonenumber,1,4)='+225' THEN 'CI'
            WHEN SUBSTR(l_phonenumber,1,4)='+226' THEN 'BF'
            WHEN SUBSTR(l_phonenumber,1,4)='+227' THEN 'NE'
            WHEN SUBSTR(l_phonenumber,1,4)='+228' THEN 'TG'
            WHEN SUBSTR(l_phonenumber,1,4)='+229' THEN 'BJ'

            WHEN SUBSTR(l_phonenumber,1,4)='+230' THEN 'MU'
            WHEN SUBSTR(l_phonenumber,1,4)='+231' THEN 'LR'
            WHEN SUBSTR(l_phonenumber,1,4)='+232' THEN 'SL'
            WHEN SUBSTR(l_phonenumber,1,4)='+233' THEN 'GH'
            WHEN SUBSTR(l_phonenumber,1,4)='+234' THEN 'NG'
            WHEN SUBSTR(l_phonenumber,1,4)='+235' THEN 'TD'
            WHEN SUBSTR(l_phonenumber,1,4)='+236' THEN 'CF'
            WHEN SUBSTR(l_phonenumber,1,4)='+237' THEN 'CM'
            WHEN SUBSTR(l_phonenumber,1,4)='+238' THEN 'CV'
            WHEN SUBSTR(l_phonenumber,1,4)='+239' THEN 'ST'

            WHEN SUBSTR(l_phonenumber,1,4)='+240' THEN 'GQ'
            WHEN SUBSTR(l_phonenumber,1,4)='+241' THEN 'GA'
            WHEN SUBSTR(l_phonenumber,1,4)='+242' THEN 'CG'
            WHEN SUBSTR(l_phonenumber,1,4)='+243' THEN 'CD'
            WHEN SUBSTR(l_phonenumber,1,4)='+244' THEN 'AO'
            WHEN SUBSTR(l_phonenumber,1,4)='+245' THEN 'GW'
            WHEN SUBSTR(l_phonenumber,1,4)='+246' THEN 'IO'
            WHEN SUBSTR(l_phonenumber,1,4)='+247' THEN 'AC'
            WHEN SUBSTR(l_phonenumber,1,4)='+248' THEN 'SC'
            WHEN SUBSTR(l_phonenumber,1,4)='+249' THEN 'SD'

            WHEN SUBSTR(l_phonenumber,1,4)='+250' THEN 'RW'
            WHEN SUBSTR(l_phonenumber,1,4)='+251' THEN 'ET'
            WHEN SUBSTR(l_phonenumber,1,4)='+252' THEN 'SO'
            WHEN SUBSTR(l_phonenumber,1,4)='+253' THEN 'DJ'
            WHEN SUBSTR(l_phonenumber,1,4)='+254' THEN 'KE'
            WHEN SUBSTR(l_phonenumber,1,4)='+255' THEN 'TZ'
            WHEN SUBSTR(l_phonenumber,1,4)='+256' THEN 'UG'
            WHEN SUBSTR(l_phonenumber,1,4)='+257' THEN 'BI'
            WHEN SUBSTR(l_phonenumber,1,4)='+258' THEN 'MZ'

            WHEN SUBSTR(l_phonenumber,1,4)='+260' THEN 'ZM'
            WHEN SUBSTR(l_phonenumber,1,4)='+261' THEN 'MG'
            WHEN SUBSTR(l_phonenumber,1,4)='+262' THEN 'RE'
            WHEN SUBSTR(l_phonenumber,1,4)='+263' THEN 'ZW'
            WHEN SUBSTR(l_phonenumber,1,4)='+264' THEN 'NA'
            WHEN SUBSTR(l_phonenumber,1,4)='+265' THEN 'MW'
            WHEN SUBSTR(l_phonenumber,1,4)='+266' THEN 'LS'
            WHEN SUBSTR(l_phonenumber,1,4)='+267' THEN 'BW'
            WHEN SUBSTR(l_phonenumber,1,4)='+268' THEN 'SZ'
            WHEN SUBSTR(l_phonenumber,1,4)='+268' THEN 'KM'

            WHEN SUBSTR(l_phonenumber,1,4)='+290' THEN 'SH'
            WHEN SUBSTR(l_phonenumber,1,4)='+291' THEN 'ER'
            WHEN SUBSTR(l_phonenumber,1,4)='+297' THEN 'AW'
            WHEN SUBSTR(l_phonenumber,1,4)='+298' THEN 'FO'
            WHEN SUBSTR(l_phonenumber,1,4)='+298' THEN 'GL'
END

        /* =================== EUROPE ================== */

        WHEN SUBSTR(l_phonenumber,1,2) IN ('+3','+4') THEN
        CASE

            WHEN SUBSTR(l_phonenumber,1,3)='+30' THEN 'GR'
            WHEN SUBSTR(l_phonenumber,1,3)='+31' THEN 'NL'
            WHEN SUBSTR(l_phonenumber,1,3)='+32' THEN 'BE'
            WHEN SUBSTR(l_phonenumber,1,3)='+33' THEN 'FR'
            WHEN SUBSTR(l_phonenumber,1,3)='+34' THEN 'ES'

            WHEN SUBSTR(l_phonenumber,1,4)='+351' THEN 'PT'
            WHEN SUBSTR(l_phonenumber,1,4)='+352' THEN 'LU'
            WHEN SUBSTR(l_phonenumber,1,4)='+353' THEN 'IE'
            WHEN SUBSTR(l_phonenumber,1,4)='+354' THEN 'IS'
            WHEN SUBSTR(l_phonenumber,1,4)='+355' THEN 'AL'
            WHEN SUBSTR(l_phonenumber,1,4)='+356' THEN 'MT'
            WHEN SUBSTR(l_phonenumber,1,4)='+357' THEN 'CY'
            WHEN SUBSTR(l_phonenumber,1,4)='+358' THEN 'FI'
            WHEN SUBSTR(l_phonenumber,1,4)='+359' THEN 'BG'

            WHEN SUBSTR(l_phonenumber,1,3)='+36' THEN 'HU'

            WHEN SUBSTR(l_phonenumber,1,4)='+370' THEN 'LT'
            WHEN SUBSTR(l_phonenumber,1,4)='+371' THEN 'LV'
            WHEN SUBSTR(l_phonenumber,1,4)='+372' THEN 'EE'
            WHEN SUBSTR(l_phonenumber,1,4)='+373' THEN 'MD'
            WHEN SUBSTR(l_phonenumber,1,4)='+374' THEN 'AM'
            WHEN SUBSTR(l_phonenumber,1,4)='+375' THEN 'BY'
            WHEN SUBSTR(l_phonenumber,1,4)='+376' THEN 'AD'
            WHEN SUBSTR(l_phonenumber,1,4)='+377' THEN 'MC'
            WHEN SUBSTR(l_phonenumber,1,4)='+378' THEN 'SM'
            WHEN SUBSTR(l_phonenumber,1,4)='+379' THEN 'VA'

            WHEN SUBSTR(l_phonenumber,1,4)='+380' THEN 'UA'
            WHEN SUBSTR(l_phonenumber,1,4)='+381' THEN 'RS'
            WHEN SUBSTR(l_phonenumber,1,4)='+382' THEN 'ME'
            WHEN SUBSTR(l_phonenumber,1,4)='+383' THEN 'XK'
            WHEN SUBSTR(l_phonenumber,1,4)='+385' THEN 'HR'
            WHEN SUBSTR(l_phonenumber,1,4)='+386' THEN 'SI'
            WHEN SUBSTR(l_phonenumber,1,4)='+387' THEN 'BA'
            WHEN SUBSTR(l_phonenumber,1,4)='+388' THEN 'EU'
            WHEN SUBSTR(l_phonenumber,1,4)='+389' THEN 'MK'

            WHEN SUBSTR(l_phonenumber,1,3)='+39' THEN 'IT'

            WHEN SUBSTR(l_phonenumber,1,3)='+40' THEN 'RO'
            WHEN SUBSTR(l_phonenumber,1,3)='+41' THEN 'CH'

            WHEN SUBSTR(l_phonenumber,1,4)='+420' THEN 'CZ'
            WHEN SUBSTR(l_phonenumber,1,4)='+421' THEN 'SK'
            WHEN SUBSTR(l_phonenumber,1,4)='+423' THEN 'LI'


            WHEN SUBSTR(l_phonenumber,1,3)='+43' THEN 'AT'
            WHEN SUBSTR(l_phonenumber,1,3)='+44' THEN 'UK'
            WHEN SUBSTR(l_phonenumber,1,3)='+45' THEN 'DK'
            WHEN SUBSTR(l_phonenumber,1,3)='+46' THEN 'SE'
            WHEN SUBSTR(l_phonenumber,1,3)='+47' THEN 'NO'
            WHEN SUBSTR(l_phonenumber,1,3)='+48' THEN 'PL'
            WHEN SUBSTR(l_phonenumber,1,3)='+49' THEN 'DE'
    END

    /* ================ SOUTH AMERICA =============== */

            WHEN SUBSTR(l_phonenumber,1,2)='+5' THEN
                    CASE

                            WHEN SUBSTR(l_phonenumber,1,4)='+500' THEN 'FK'
                            WHEN SUBSTR(l_phonenumber,1,4)='+501' THEN 'BZ'
                            WHEN SUBSTR(l_phonenumber,1,4)='+502' THEN 'GT'
                            WHEN SUBSTR(l_phonenumber,1,4)='+503' THEN 'SV'
                            WHEN SUBSTR(l_phonenumber,1,4)='+504' THEN 'HN'
                            WHEN SUBSTR(l_phonenumber,1,4)='+505' THEN 'NI'
                            WHEN SUBSTR(l_phonenumber,1,4)='+506' THEN 'CR'
                            WHEN SUBSTR(l_phonenumber,1,4)='+507' THEN 'PA'
                            WHEN SUBSTR(l_phonenumber,1,4)='+508' THEN 'PM'
                            WHEN SUBSTR(l_phonenumber,1,4)='+509' THEN 'HT'

                            WHEN SUBSTR(l_phonenumber,1,3)='+51' THEN 'PE'
                            WHEN SUBSTR(l_phonenumber,1,3)='+52' THEN 'MX'
                            WHEN SUBSTR(l_phonenumber,1,3)='+53' THEN 'CU'
                            WHEN SUBSTR(l_phonenumber,1,3)='+54' THEN 'AR'
                            WHEN SUBSTR(l_phonenumber,1,3)='+55' THEN 'BR'
                            WHEN SUBSTR(l_phonenumber,1,3)='+56' THEN 'CL'
                            WHEN SUBSTR(l_phonenumber,1,3)='+57' THEN 'CO'
                            WHEN SUBSTR(l_phonenumber,1,3)='+58' THEN 'VE'

                            WHEN SUBSTR(l_phonenumber,1,4)='+590' THEN 'GB'
                            WHEN SUBSTR(l_phonenumber,1,4)='+591' THEN 'BO'
                            WHEN SUBSTR(l_phonenumber,1,4)='+592' THEN 'GY'
                            WHEN SUBSTR(l_phonenumber,1,4)='+593' THEN 'EC'
                            WHEN SUBSTR(l_phonenumber,1,4)='+594' THEN 'GF'
                            WHEN SUBSTR(l_phonenumber,1,4)='+595' THEN 'PY'
                            WHEN SUBSTR(l_phonenumber,1,4)='+596' THEN 'MQ'
                            WHEN SUBSTR(l_phonenumber,1,4)='+597' THEN 'SR'
                            WHEN SUBSTR(l_phonenumber,1,4)='+598' THEN 'UY'
                            WHEN SUBSTR(l_phonenumber,1,4)='+599' THEN 'BQ'
            END

    /* ================ OCEANIA =============== */

    WHEN SUBSTR(l_phonenumber,1,2)='+6' THEN
    CASE

        WHEN SUBSTR(l_phonenumber,1,3)='+60' THEN 'MY'
        WHEN SUBSTR(l_phonenumber,1,3)='+61' THEN 'AU'
        WHEN SUBSTR(l_phonenumber,1,3)='+62' THEN 'ID'
        WHEN SUBSTR(l_phonenumber,1,3)='+63' THEN 'PH'
        WHEN SUBSTR(l_phonenumber,1,3)='+64' THEN 'NZ'
        WHEN SUBSTR(l_phonenumber,1,3)='+65' THEN 'SG'
        WHEN SUBSTR(l_phonenumber,1,3)='+66' THEN 'TH'

            WHEN SUBSTR(l_phonenumber,1,4)='+670' THEN 'TL'
            WHEN SUBSTR(l_phonenumber,1,4)='+671' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+672' THEN 'NF'
            WHEN SUBSTR(l_phonenumber,1,4)='+673' THEN 'BN'
            WHEN SUBSTR(l_phonenumber,1,4)='+674' THEN 'NR'
            WHEN SUBSTR(l_phonenumber,1,4)='+675' THEN 'PG'
            WHEN SUBSTR(l_phonenumber,1,4)='+676' THEN 'TO'
            WHEN SUBSTR(l_phonenumber,1,4)='+677' THEN 'SB'
            WHEN SUBSTR(l_phonenumber,1,4)='+678' THEN 'VU'
            WHEN SUBSTR(l_phonenumber,1,4)='+679' THEN 'FJ'

            WHEN SUBSTR(l_phonenumber,1,4)='+680' THEN 'PW'
            WHEN SUBSTR(l_phonenumber,1,4)='+681' THEN 'WF'
            WHEN SUBSTR(l_phonenumber,1,4)='+682' THEN 'CK'
            WHEN SUBSTR(l_phonenumber,1,4)='+683' THEN 'NU'
            WHEN SUBSTR(l_phonenumber,1,4)='+684' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+685' THEN 'WS'
            WHEN SUBSTR(l_phonenumber,1,4)='+686' THEN 'KI'
            WHEN SUBSTR(l_phonenumber,1,4)='+687' THEN 'NC'
            WHEN SUBSTR(l_phonenumber,1,4)='+688' THEN 'TV'
            WHEN SUBSTR(l_phonenumber,1,4)='+689' THEN 'PF'

            WHEN SUBSTR(l_phonenumber,1,4)='+690' THEN 'TK'
            WHEN SUBSTR(l_phonenumber,1,4)='+691' THEN 'FM'
            WHEN SUBSTR(l_phonenumber,1,4)='+692' THEN 'MH'
    END

    /* ================ SOVIET =============== */

                        WHEN SUBSTR(l_phonenumber,1,2)='+7' THEN
                        CASE

            WHEN SUBSTR(l_phonenumber,1,3)='+70' THEN 'RU'
            WHEN SUBSTR(l_phonenumber,1,3)='+71' THEN 'RU'
            WHEN SUBSTR(l_phonenumber,1,3)='+72' THEN 'RU'
            WHEN SUBSTR(l_phonenumber,1,3)='+73' THEN 'RU'
                        WHEN SUBSTR(l_phonenumber,1,3)='+74' THEN 'RU'
                        WHEN SUBSTR(l_phonenumber,1,3)='+75' THEN 'RU'
                        WHEN SUBSTR(l_phonenumber,1,3)='+76' THEN 'KZ'
                        WHEN SUBSTR(l_phonenumber,1,3)='+77' THEN 'KZ'
                        WHEN SUBSTR(l_phonenumber,1,3)='+78' THEN 'RU'
                        WHEN SUBSTR(l_phonenumber,1,3)='+79' THEN 'RU'
    END

    /* ================ ASIA =============== */

                        WHEN SUBSTR(l_phonenumber,1,2)='+8' THEN
                        CASE

            WHEN SUBSTR(l_phonenumber,1,4)='+800' THEN 'XT'
            WHEN SUBSTR(l_phonenumber,1,4)='+801' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+802' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+803' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+804' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+805' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+806' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+807' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+808' THEN 'XS'
                        WHEN SUBSTR(l_phonenumber,1,4)='+809' THEN ''

                                WHEN SUBSTR(l_phonenumber,1,3)='+81' THEN 'JP'
                                WHEN SUBSTR(l_phonenumber,1,3)='+82' THEN 'KR'
                                WHEN SUBSTR(l_phonenumber,1,3)='+84' THEN 'VN'

            WHEN SUBSTR(l_phonenumber,1,4)='+850' THEN 'KP'
            WHEN SUBSTR(l_phonenumber,1,4)='+851' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+852' THEN 'HK'
            WHEN SUBSTR(l_phonenumber,1,4)='+853' THEN 'MO'
                        WHEN SUBSTR(l_phonenumber,1,4)='+854' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+855' THEN 'KH'
                        WHEN SUBSTR(l_phonenumber,1,4)='+856' THEN 'LA'
                        WHEN SUBSTR(l_phonenumber,1,4)='+857' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+858' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+859' THEN ''

                                WHEN SUBSTR(l_phonenumber,1,3)='+86' THEN 'CN'

            WHEN SUBSTR(l_phonenumber,1,4)='+870' THEN 'XN'
            WHEN SUBSTR(l_phonenumber,1,4)='+871' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+872' THEN ''
            WHEN SUBSTR(l_phonenumber,1,4)='+873' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+874' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+875' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+876' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+877' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+878' THEN 'XP'
                        WHEN SUBSTR(l_phonenumber,1,4)='+879' THEN ''

            WHEN SUBSTR(l_phonenumber,1,4)='+880' THEN 'BD'
            WHEN SUBSTR(l_phonenumber,1,4)='+881' THEN 'XG'
            WHEN SUBSTR(l_phonenumber,1,4)='+882' THEN 'XV'
            WHEN SUBSTR(l_phonenumber,1,4)='+883' THEN 'XV'
                        WHEN SUBSTR(l_phonenumber,1,4)='+884' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+885' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+886' THEN 'TW'
                        WHEN SUBSTR(l_phonenumber,1,4)='+887' THEN ''
                        WHEN SUBSTR(l_phonenumber,1,4)='+888' THEN 'XD'
                        WHEN SUBSTR(l_phonenumber,1,4)='+889' THEN ''
                END

        /* ============= MIDDLE EAST ============ */

        WHEN SUBSTR(l_phonenumber,1,2)='+9' THEN
        CASE

                WHEN SUBSTR(l_phonenumber,1,3)='+90' THEN 'TR'
                WHEN SUBSTR(l_phonenumber,1,3)='+91' THEN 'IN'
                WHEN SUBSTR(l_phonenumber,1,3)='+92' THEN 'PK'
                WHEN SUBSTR(l_phonenumber,1,3)='+93' THEN 'AF'
                WHEN SUBSTR(l_phonenumber,1,3)='+94' THEN 'LK'
                WHEN SUBSTR(l_phonenumber,1,3)='+95' THEN 'MM'

                WHEN SUBSTR(l_phonenumber,1,4)='+960' THEN 'MV'
                WHEN SUBSTR(l_phonenumber,1,4)='+961' THEN 'LB'
                WHEN SUBSTR(l_phonenumber,1,4)='+962' THEN 'JO'
                WHEN SUBSTR(l_phonenumber,1,4)='+963' THEN 'SY'
                WHEN SUBSTR(l_phonenumber,1,4)='+964' THEN 'IQ'
                WHEN SUBSTR(l_phonenumber,1,4)='+965' THEN 'KW'
                WHEN SUBSTR(l_phonenumber,1,4)='+966' THEN 'SA'
                WHEN SUBSTR(l_phonenumber,1,4)='+967' THEN 'YE'
                WHEN SUBSTR(l_phonenumber,1,4)='+968' THEN 'OM'

                WHEN SUBSTR(l_phonenumber,1,4)='+970' THEN 'PS'
                WHEN SUBSTR(l_phonenumber,1,4)='+971' THEN 'AE'
                WHEN SUBSTR(l_phonenumber,1,4)='+972' THEN 'IL'
                WHEN SUBSTR(l_phonenumber,1,4)='+973' THEN 'BH'
                WHEN SUBSTR(l_phonenumber,1,4)='+974' THEN 'QA'
                WHEN SUBSTR(l_phonenumber,1,4)='+975' THEN 'BT'
                WHEN SUBSTR(l_phonenumber,1,4)='+976' THEN 'MN'
                WHEN SUBSTR(l_phonenumber,1,4)='+977' THEN 'NP'
                WHEN SUBSTR(l_phonenumber,1,4)='+979' THEN 'XR'

                WHEN SUBSTR(l_phonenumber,1,3)='+98' THEN 'IR'

                WHEN SUBSTR(l_phonenumber,1,4)='+991' THEN 'XC'
                WHEN SUBSTR(l_phonenumber,1,4)='+992' THEN 'TJ'
                WHEN SUBSTR(l_phonenumber,1,4)='+993' THEN 'TM'
                WHEN SUBSTR(l_phonenumber,1,4)='+994' THEN 'AZ'
                WHEN SUBSTR(l_phonenumber,1,4)='+995' THEN 'GE'
                WHEN SUBSTR(l_phonenumber,1,4)='+996' THEN 'KG'
                WHEN SUBSTR(l_phonenumber,1,4)='+997' THEN 'KZ'
                WHEN SUBSTR(l_phonenumber,1,4)='+998' THEN 'UZ'
        END

        ELSE ''

        END ) ;

            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line('Error in Function :-( ' || SQLCODE || chr(13) || SQLERRM) ;
                    --raise_application_error(-20009, 'Error in Function ! ' || SQLCODE || chr(13) || SQLERRM) ;
                    RETURN '';


END FU_PhoneCountry;

Once you have the function compiled, just do this in your SQL statements:

FU_PHONECOUNTRY(the_phone_number_column)

Example:

SELECT FU_PHONECOUNTRY('+40721842038') AS COuntry FROM DUAL UNION ALL
SELECT FU_PHONECOUNTRY('+48572842954') AS COuntry FROM DUAL UNION ALL
SELECT FU_PHONECOUNTRY('+48572837576') AS COuntry FROM DUAL UNION ALL
SELECT FU_PHONECOUNTRY('+32491635024') AS COuntry FROM DUAL UNION ALL
SELECT FU_PHONECOUNTRY('+40721776901') AS COuntry FROM DUAL UNION ALL
SELECT FU_PHONECOUNTRY('+32485001108') AS COuntry FROM DUAL ;

Returns

COUNTRY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
-------
RO
PL
PL
BE
RO
BE

6 rows selected.

Neomineomycin answered 25/10, 2023 at 12:8 Comment(0)

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.

Resonant answered 21/3, 2020 at 11:58 Comment(0)

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

Shier answered 9/3, 2023 at 16:32 Comment(0)

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;

Outshout answered 31/7, 2023 at 7:40 Comment(1)

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Saccharide 4/8, 2023 at 12:4

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags