Bdtjtk

Given I have two tables - routes and country_codes.
Table routes contains column prefix which I want to match to the longest value found in table country_codes column code

The objective is to find the country name of each route.

The data structure I have

CREATE TABLE routes(

   prefix   INTEGER  NOT NULL,

   supplier VARCHAR(64) NOT NULL

);

table routes

prefix      supplier

1876        att

1787        att

1           att

81          bt

8150        bt

8170        bt

8180        bt

8190        bt

82          verizon

821         verizon

84          att

84120       att

84121       att

84122       att

84123       att

84124       att

84125       att

85248       verizon

85249       verizon

85251       verizon





CREATE TABLE country_codes (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    country VARCHAR(128) DEFAULT 0 NOT NULL,

    code INTEGER

);

table country_codes

167 American Samoa              1684

170 Anguilla                    1264

173 Antigua and Barbuda         1268

181 Bahamas                     1242

184 Barbados                    1246

189 Bermuda                     1441

195 British Virgin Islands      1284

205 Cayman Islands              1345

225 Dominica                    1767

226 Dominican Republic          1809

227 Dominican Republic          1829

228 Dominican Republic          1849

251 Grenada                     1473

253 Guam                        1671

273 Jamaica                     1876

310 Montserrat                  1664

326 Northern Mariana Islands    1670

340 Puerto Rico                 1787

341 Puerto Rico                 1939

350 Saint Kitts and Nevis       1869

351 Saint Lucia                 1758

354 Saint Vincent and the Grenadines    1784

364 Sint Maarten                1721

389 Trinidad and Tobago         1868

393 Turks and Caicos Islands    1649

399 United States               1

401 US Virgin Islands           1340

274 Japan                       81

370 South Korea                 82

405 Vietnam                     84

201 Cambodia                    855

261 Hong Kong                   852

282 Laos                        856

291 Macau                       853

325 North Korea                 850

So I want to query table routes to give me result prefix, country

results I am looking for

prefix      country

1876        Jamaica

1787        Puerto Rico

1           United States

81          Japan

8150        Japan

8170        Japan

8180        Japan

8190        Japan

82          South Korea

821         South Korea

84          Vietnam

84120       Vietnam

84121       Vietnam

84122       Vietnam

84123       Vietnam

84124       Vietnam

84125       Vietnam

85248       Hong Kong

85249       Hong Kong

85251       Hong Kong

What sql query can i use to do this?

The complication its not a straight match that can be done with a join.
Its longest match needs to be used.

You must check the prefix from routes and all of its substrings and use COALESCE() until you get the row from country_codes

SELECT 

  r.prefix,

  COALESCE(

    (SELECT country FROM country_codes c WHERE c.code = r.prefix), 

    (SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 4)),

    (SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 3)),

    (SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 2)),

    (SELECT country FROM country_codes c WHERE c.code = SUBSTR(r.prefix, 1, 1))

  ) AS country

FROM routes AS r;