3

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.?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
JD Isaacks
  • 56,088
  • 93
  • 276
  • 422

3 Answers3

5

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.

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?

Community
  • 1
  • 1
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
3

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).

the_void
  • 5,512
  • 2
  • 28
  • 34
1
select * from index_table where item_name rlike '[[:<:]]preform[s]*[es]*[ies]*[[:>:]]';

Check if this helps. My case it worked out. Although will not cover for all plurals but yes for 90-95% cases.

Cheers, Ashish

Ashish
  • 402
  • 2
  • 6
  • 15