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.?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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');
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?
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.