mysql fuzzy search

Anon

I'm doing a search in a mysql database where I compare words, like this:

SELECT .. FROM .... WHERE word IN ('word1', 'word2', ...)

It all works like a charm, but I would like to expand the search to be 'fuzzy'. So that if word='matchbox' and word1='match' would generate a hit.

Any suggestions?

Thanks,

Andrew
andrew_pasetti@harvard.edu

Anon

How about separate WHERE statements with a JOIN?

shanx24

For one thing, you could use SOUNDEX in mysql...

Anon

Josh -

Can you give me an example?

Thanks.

Anon

I'd look into the full text indexing support in the new version of MySQL. I have not yet fully investigate but this should solve your problem -
http://www.mysql.com/doc/M/y/MySQL_full-text_search.html

Anon

This works well for me:

select * from TABLE where soundex(FIELD) like soundex('$VAR')

shanx24

What if the word to be searched is just 4 letters, whereas the column is a TEXT type and has plenty of words?

Anon

Shashank Tripathi wrote:

What if the word to be searched is just 4 letters, whereas the column is a TEXT type and has plenty of words?

Still seems to work.

Anon

Thanks for the suggestions. I'll check out the full-text option in mysql. Is there a synonym dictionary that I could use so that I would get a hit on a document if someone were to type in "phones" and I have a reference to a document that uses "phone"?

--Andrew

[deleted]

That would be a huge dictionary...🙁

Anon

It sure would be. What do people do for situations like this? I would hate to not show a record because of a synonym problem -- although it happens all the time. I was hoping that someone may have already created something similar to a thesaurus, which I can reference to the search word and loop through all the synonyms -- searching on each one.

Thanks,

Andrew

[deleted]

That's what soundex etc are for, they let you match on words that are similar, so also misspelled words.

If you have a synonims table, you'd have hunderds of thousands of words referencing eachother, that would be way to slow to use (providing you can afford to buy the data itself🙂 )

vincentpeace

You can also pattern match in SQL eg SELECT something WHERE somethingelse LIKE 'blah%' which will (I think) match anything beginning with blah. For plurals, you could remove trailing s's and use a pattern.

shanx24

CJD,

I think I am sure that it wont work! If the column has the following text:

"This is the text stored in the column"

And the search word is:

"text"

Then, the soundex of the search word will be VERY different from the soundex of the column's value!

Pls let me know if you have tried it, cuz I did and it does not work ("cannot" work, if I am not mistaken). It'll be great if it works, cuz it will solve ALL MY PROBLEMS as well! 🙂

Cheers
S

Anon

They use WordNet! Look it up & download it... it's free. There's some great frontends for Perl, not sure about PHP. You can do stuff like get_synset("cat"), do morphological searches, etc. Great tool. Not too slow once you've loaded the initially DB in memory.