SQL plural/singular searches

Question

How can I make plural/singular words pull all forms?

Example: If a person searches for "mens rings" how can I check the data base to see if any of the fields contain mens, men, men's, ring, rings, etc.?

Answer 1

It dosn't seem that using LIKE would be the best approach for what you need. I would look into MySQL FULLTEXT indexing to get the basic functionality working. I'll have to look into the plural thing a bit more to see if that can be accomplished.

Take a look at these two links for FULLTEXT indexing info.

• http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html
• http://www.petefreitag.com/item/477.cfm
• http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

Here is a snippet from the second article:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

UPDATE:

I found this SO post in which a comment seems to suggest that the FULLTEXT indexing will take plural and grammatical forms into account but he dosn't cite a source, so it's hard for me to say for sure. Can you try the FULLTEXT indexing method?

Answer 2

The Sphinx full-text search engine for MySQL handles such cases, and a few more (see here).

It uses the Porter Stemmer algorithm [2][3] to map things like "fishing", "fished", "fish", and "fisher" to a root stem of "fish" (see wiki).