MySQL: How to search for spelling variants? ("murrays", "murray's" etc)

Question

Hi Friends,

I want to search like this: the user inputs e.g. "murrays", and the search result will show both records containing "murrays" and records containing "murray's". What should I do in my query.pl?

Answer 1

I would first build a search column which has the text without punctuation and then search on that. Otherwise you'll have have to have a series of regular expressions to search against or check individual records in PHP for matching: both of which are computational intensive operations.

Answer 2

If this is for single word searching, you could try using Soundex or Metaphone functions? These would handle sounds-like as well as spelling

Not sure if MySQL has these, but PHP does (which would require separate columns to hold these values).

Otherwise, Richy's no-punctuation extra column seems best.

Answer 3

Maybe something like this: (untested!)

SELECT * FROM users WHERE REPLACE(user_name, '\'', '') = "murrays"

Answer 4

You could try adding a replace to your query like this

replace(name, '''','')

to temporarily get rid of the apostrophes for the match.

select name from nametable where name = replace(name,'''','');

Answer 5

This won't help if you absolutely need to do these queries in SQL, but if you can set up a Lucene search index for it, you gain a lot of this kind of "fuzzy search" functionality. Note though that Lucene is quite a complex topic by itself.

Answer 6

What do you think about using the SOUNDEX function and the SOUNDS LIKE operator ?

That way, you can simply do:

SELECT * from USERS WHERE name SOUNDS LIKE 'murrays'

I'm pretty sure it doesn't work for every case, and perhaps it is not the most efficient way to solve the problem, but it could fit your needs.

Answer 7

What you could do is create an extra field in the database, which contains the data with all special characters stripped from it, and search there. A bit lame, I know. Looking forward to see smarter answers ;)

Answer 8

Quick and dirty:

SELECT * FROM myTable WHERE REPLACE(name, '\'', '') = 'murrays'

Answer 9

This query should be able to pick up "murrays" or "murray's".

var inputStr = "murrays";
inputStr = String.Replace("'", "\'", inputStr);
SELECT * FROM ATable WHERE Replace(AField, '\'', '') = inputStr OR AField = inputStr

Answer 10

1. strip user input and names in database from all non-letter characters.
2. Use levenstein distance or soundex to find murrays with murray or marrays. This is optional but your users would love that.