MySQL: conduct a fuzzy search

Question

I have a names table in my database and I would wish to conduct a fuzzy search on it for example my database contains:

Name         ID
John Smith   1
Edward Smith 2
Gabriel Gray 3
Paul Roberts 4

At the moment when I search the database via python I can only do exact match searching. But I would like to be able to do fuzzy searching where by I can search for the name "smith" and bring back John Smith and Edward Smith.

Answer 1

In simplest form, you'd use the LIKE comparison:

SELECT * FROM table WHERE name LIKE '%smith%';

More elaborate searches can de done with FULLTEXT index (large amounts of text), SOUNDEX() (works on words in the english language, matching on other languages is everything from 'somewhat workable' to 'terrible'), levenshtein distance of words etc.

Answer 2

import MySQLdb
search_str = 'smith'
conn = MySQLdb.connect(host="localhost", user="me",passwd="pw",db="mydb")
c = conn.cursor()
c.execute("SELECT name FROM mytable WHERE name LIKE %s", '%' + search_str + '%')
c.fetchall()
c.close()
conn.close()