CODEWITHSUNDEEP
mysqldatabasefull-text-search
Naterade
Naterade

Reputation: 2675

Mysql search, match words if similar

I have a db with a table called sections. In that is a field called head that has a full text index with 3 entries each a string. 2 entries have the word motorcycle and one has motorcycles. I can't seem to find a way to return all 3 if the term "motorcycles" is search.

I have tried

SELECT * FROM sections
     WHERE MATCH (head) AGAINST ('Motorcycles')

but it only returns the plural entry. I have also tried.

SELECT * FROM sections
   WHERE head like '%motorcycles%'

but that also only returns the plural entry. Is there a way to return all three rows based on "motorcycles"?

Upvotes: 0

Views: 2319

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

If i have got your Questions correctly I think you want something like this:

if (SELECT count(1) FROM sections WHERE head like '%motorcycles%')>1
begin
    select * FROM selections
     WHERE head like '%motorcycle%'
end

Upvotes: 0

Jizzle
Jizzle

Reputation: 214

Assuming your question is more general than the specific motorcylce example you've given...I'm not aware of a way that you can relax the contraints directly in the SQL (without a stored proc to pre process the input). I'd suggest pre processing your input with a regex to remove/replace the chars that make the word plural. Then use like in the way that you have shown on the singular version of the word.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Have you tried boolean mode?

where match (head) against ('+ Motorcycle*' in Boolean mode)

More information is here.

Your where clause has an extra "s":

 SELECT * FROM sections WHERE head like '%motorcycle%'

Upvotes: 1

Related Questions