Skip to content

SQL LIKE Operator

Python Tutorials

Introduction

LIKE opearator in SQL allows us to make queries based on first character, last character, sub-patterns or even position of the character.

You can also use NOT LIKE to exclude values with certain characters from your query results.

Overall LIKE operator makes relational databases and SQL querying much more useful, functional and workable.

In this tutorial we will explore LIKE operator with multiple SQL and Python examples.

You can execute SQL queries below from your IDE with Python as well as from DB Browser for SQLite’s Execute SQL section.

You can use LIKE operator to create queries that are not exact matches.

For example you can use ‘t%’ to query rows that start with t and you can use ‘%t’ to query items that end with t.

Let’s see an example.

Internet Usage for countries that start with ‘be‘:

1- Querying text based on starting characters

Internet Usage for countries that start with ‘be‘:

a = cur.execute('''SELECT * FROM Intuse WHERE country like "be%" 
                order by percentage desc limit 5''')
('Bermuda', 60349.0, 61349, 197, '98.37%', 3)
('Belgium', 10021242.0, 11429336, 79, '87.68%', 30)
('Belarus', 7048231.0, 9468338, 92, '74.44%', 71)
('Belize', 176400.0, 374681, 174, '47.08%', 135)
('Benin', 1578008.0, 11175692, 80, '14.12%', 192)

Holy Python is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

2- Querying text based on ending characters

conn = sqlite3.connect('Desktop/GA3.sqlite')
cur = conn.cursor()

a = cur.execute('''SELECT * FROM Intuse WHERE country like "%y" 
                order by percentage desc limit 10''')
('Norway', 5120225.0, 5305383, 117, '96.51%', 12)
('Germany', 77794405.0, 82114224, 18, '86%', 38)
('Hungary', 7461297.0, 9721559, 90, '76.75%', 62)
('Uruguay', 2360269.0, 3456750, 135, '68.28%', 84)
('Palestinian Authority', 3208312.0, 4920724, 120, '65.20%', 89)
('Turkey', 62075879.0, 80745020, 19, '64.68%', 92)
('Italy', 36387619.0, 59359900, 23, '61.30%', 101)
('Paraguay', 4160340.0, 6811297, 106, '61.08%', 102)
('Jersey', 38958.0, 165314, 183, '41.03%', 141)

It was surprising for me to see Germany with sub-90% internet usage. Also, some other industrialized European countries such as Hungary, Turkey and Italy seem to have somewhat lower than expected internet usage ratios. Maybe mountainous terrain is another restrain for the internet penetration and infrastructure investment decisions.

By the way isn’t it weird that there is no country whose name ends with “p”, “b” or “v” even though there is a country whose name ends with “q”.

3- Querying text based on sub-patterns

Sometimes also, you may want to query based on a string pattern or characters in an unknown position in the text. I was curious about which cool countries have “X” in their names and also I wanted to see a complete list of all the countries with “stan” in their names.

Here are the results:

a = cur.execute('''SELECT * FROM Intuse WHERE country like "%x%" 
                order by percentage desc limit 10''')
('Luxembourg', 570794.0, 583455, 167, '97.83%', 7)
('Mexico', 89000000.0, 128972439, 10, '69%', 80)
a = cur.execute('''SELECT * FROM Intuse WHERE country like "%stan%" 
                order by percentage desc limit 10''')
('Kazakhstan', 13913699.0, 18204499, 63, '76.43%', 63)
('Uzbekistan', 16692456.0, 31910641, 44, '52.31%', 121)
('Pakistan', 93000000.0, 220892341, 5, '43.50%', 135)
('Kyrgyzstan', 2309235.0, 6045117, 111, '38.20%', 145)
('Tajikistan', 1959127.0, 8921343, 95, '21.96%', 177)
('Turkmenistan', 1223591.0, 5758075, 112, '21.25%', 181)
('Afghanistan', 4068194.0, 32225560, 44, '11.45%', 199)

Cursor is another crucial part of initiating the SQL operations in Python. Using the connection to the database we just created we can create a cursor which will be used to execute SQL commands.

Below you can see the part that would come after connection and cursor. We are using executescript method to execute an SQL script which creates a table named Fam with two columns: Name and Age.

5- Querying based on character position

You can also query based on characters with a specific position. All you have to do is skip character positions with underscore: _

Check out the example:

a = cur.execute('''SELECT * FROM Intuse WHERE country like "__y%" 
Order by percentage desc limit 10''')

This query will return us results for countries with third character as “y” in their names. Realize double underscore is used and “y” is in third place.

('Cayman Islands', 49906.0, 61559, 196, '81.07%', 48)
('Seychelles', 55677.0, 94737, 193, '58.77%', 109)
('Egypt', 54740141.0, 97553151, 13, '44.95%', 136)
('Guyana', 290375.0, 777859, 163, '37.33%', 149)

6- Querying with NOT LIKE

Maybe you also want to exclude certain characters or sub-patterns from your query.

Check out the example:

a = cur.execute('''SELECT * FROM Intuse 
WHERE country NOT LIKE "%Islands%"
ORDER BY percentage DESC LIMIT 5''')

This query will return countries without “Islands” in their names.

('Andorra', 76095.0, 76965, 194, '98.87%', 2)
('Bermuda', 60349.0, 61349, 197, '98.37%', 3)
('Iceland', 329196.0, 335025, 175, '98.26%', 4)
('Liechtenstein', 37201.0, 37922, 203, '98.10%', 5)
('Kuwait', 4053797.0, 4136528, 129, '98.00%', 6)

7- Escape Character

you can also query % character by using escape character \.

“%100\%%” will return any value that has 100% in it.

8- PRAGMA Settings

Pragmas are bunch of settings that you can adjust for your queries in SQLite. For example, SQLite LIKE operator queries are not case sensitive. If you’d like to make case sensitive queries with LIKE operator you can simply turn on the sensitivity with below script.

PRAGMA case_sensitive_like=ON;
Example: SELECT * FROM Fam where (name) LIKE ‘P%’ will query only for capital P.

Always be closing

You know the ultimate sales strategy popularized by Alex Baldwin’s dramatic performance in “Glengarry Glen Ross”, ABC, always be closing!

It turns out Always be closing also applies to computer science in some instances. When you work with external windows (turtle, cv2 sometimes), files and databases and similar operations that require an external connection or application you want to remember to close them to avoid conflict and continuous use of resources.

So let’s go ahead and do that for our database connection as well.

conn.close()

LIKE Operator Summary

In this SQL tutorial we explored LIKE operator and its use cases with Python.