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.
x
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'),]
Advertisement
Answer
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 || "%"')