MySql full text search to consider extra s es

Question

I need a full text search query to search a word, also it should consider extra s and es at the end. If I put * at the end it matches with lot of extra characters as well which are not needed. eg;

select item_description from item_description_mapping where match(item_description) 
against('+maintain*' in boolean mode) ;

This query gives results

• Maintain
• maintainance
• Maintains

Here we require only maintain, maintains like that not maintainance.

Is there something like regex combination with boolean mode in full text search or we can use length function with exact match??

Thanks, Ashish

Answer 1

I got answer for this,

select * from index_table where item_name rlike '[[:<:]]preform[s]*[es]*[ies]*[[:>:]]';

I am posting this here coz it might be helpful for others in need.

Thanks, Ashish