A fuzzy search is a technique that uses search algorithms to find strings that match patterns approximately.
It's particularly useful when looking for data without knowing exactly what they're looking for or how a word is spelled.
This Tutorial's Database: spicy.db
Table Name: hotpeppers; Showing only first 3 peppers
name
maxSHU
inventory
Jalapeno
8000
12
Serrano
5000
7
Habanero
350000
8
There are 23 named hot peppers in the dataset
name -> TEXT column of hot pepper names
maxSHU -> INTEGER column of each pepper's maximum level of range
inventory -> INTEGER column of random counts of how many peppers are available
Exact Matching and Case-Insensitive Matching
Query
SELECT *
FROM hotpeppers
WHERE name = "serrano"
# Python w/ SQLite
import sqlite3
connection = sqlite3.connect("spicy.db")
cursor = connection.cursor()
query = '''
SELECT *
FROM hotpeppers
WHERE name = "serrano"
'''
result = cursor.execute(query)
print(f"Our wanted pepper data: {result.fetchall()}")
cursor.close()
connection.close()
Output
Our wanted pepper data: []
Although the 2nd row contains a pepper called Serrano since the S is capitalized our query could not select the pepper we wanted because the = needs an exact match
LIKE operation
We can use the LIKE operator to do case-insensitive comparison
Query
SELECT *
FROM hotpeppers
WHERE name LIKE "serrano"
# Python w/ SQLite
import sqlite3
connection = sqlite3.connect("spicy.db")
cursor = connection.cursor()
query = '''
SELECT *
FROM hotpeppers
WHERE name LIKE "serrano"
'''
result = cursor.execute(query)
print(f"Our wanted pepper data: {result.fetchall()}")
cursor.close()
connection.close()
Output
Our wanted pepper data: [('Serrano', 5000, 7)]
Finding all pepper names that end in "no"
Query
SELECT *
FROM hotpeppers
WHERE name LIKE "%no"
# Python w/ SQLite
import sqlite3
connection = sqlite3.connect("spicy.db")
cursor = connection.cursor()
query = '''
SELECT *
FROM hotpeppers
WHERE name LIKE "%no"
'''
result = cursor.execute(query)
print(f"Our wanted pepper data: {result.fetchall()}")
cursor.close()
connection.close()