Comparing substrings in two columns across tables in sqlite

Question

I'm using Python and trying to compare two tables in SQL and match them by partial country names. So far I've gotten country names that match exactly. I'm running into an issue trying to get partial matches. One example that I'm trying to get a match for is 'Burma' in one table and 'Burma (Myanmar)' in another table.

conn = sqlite3.connect('CheapestDestinations.db')
c = conn.cursor()

c.execute('select FinalDestinations_table.CountryName, advisory_table.Country \
    FROM FinalDestinations_table JOIN advisory_table \
    WHERE FinalDestinations_table.CountryName like advisory_table.Country') 
c.fetchall()

This is my result:

[('Mexico', 'Mexico'),
 ('Canada', 'Canada'),
 ('Costa Rica', 'Costa Rica'),
 ('Barbados', 'Barbados'),]

forpas · Accepted Answer · 2020-05-05 21:29:45Z

1

Concatenate the % wildcard to use with the operator LIKE and change the WHERE clause to ON clause because you are joining the tables:

c.execute('select FinalDestinations_table.CountryName, advisory_table.Country \
    FROM FinalDestinations_table JOIN advisory_table \
    ON FinalDestinations_table.CountryName like "%" || advisory_table.Country || "%" \
    OR advisory_table.Country like "%" || FinalDestinations_table.CountryName || "%"')

answered May 5, 2020 at 21:29

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NerdyGinger · Accepted Answer · 2020-05-05 21:31:41Z

1

Partial matches can be found using the % wildcard. Using your example:

conn = sqlite3.connect('CheapestDestinations.db') c = conn.cursor()

c.execute("select FinalDestinations_table.CountryName, advisory_table.Country 
\ FROM FinalDestinations_table JOIN advisory_table 
\ WHERE FinalDestinations_table.CountryName like '%' || advisory_table.Country || '%'") c.fetchall()

This change would match the FinalDestination_table.CountryName with country in the advisory_table that contained that text; e.g. 'Burma' would match with 'Burma (Myanmar)', 'Burma', and 'Another Burma Name'.

See this link for more examples and usages.

edited May 5, 2020 at 21:31

answered May 5, 2020 at 21:22

NerdyGinger

40610 silver badges16 bronze badges

Collectives™ on Stack Overflow

Comparing substrings in two columns across tables in sqlite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related