SQL plural/singular searches

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

Contents hide

Answers:

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.

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

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?

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

Method 3

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating