CODEWITHSUNDEEP
pythonsqlsqlite
Mac
Mac

Reputation: 23

Comparing substrings in two columns across tables in sqlite

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'),]

Upvotes: 1

Views: 244

Answers (2)

NerdyGinger
NerdyGinger

Reputation: 406

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.

Upvotes: 1

forpas
forpas

Reputation: 164089

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 || "%"')

Upvotes: 1

Related Questions