5

In boolean mode apple* will find apple, apples, applestore. But it will not find me (dummy words) Mapple, Trapple. So I tried using *apple but that doesn't work.

Is it not possible to find for words ending in apple with full text search? Or am I missing something from the documentation?

Bastian Voigt
  • 5,311
  • 6
  • 47
  • 65
Tessmore
  • 1,054
  • 1
  • 9
  • 23

2 Answers2

12

Although MySQL full-text search does not allow you to search by suffixes, there's a workaround for this if you are OK with some storage overhead. You will simply need an extra column that will keep a reversed string value from the original column, and also an additional full-text index for the new column. Then your query will look in the following way:

SELECT * FROM Tbl
WHERE MATCH (Word_reversed) AGAINST (REVERSE('*apple') IN BOOLEAN MODE)

Remember that you will also have to maintain additional field either by means of update/insert triggers or manually.

alkoln
  • 599
  • 5
  • 14
1

Try This

SELECT * From Table
WHERE Word like '%apple'
PaShKa
  • 188
  • 1
  • 3
  • 13
  • Are you trying to tell me that there is thus no such thing available in FULL TEXT search? Just tested '%apple' does not work, but using '%apple%' is giving an okish result for now. Will have to test some other cases though. – Tessmore Jul 18 '12 at 20:51
  • Im not sure if there is such a thing in FULL TEXT – PaShKa Jul 18 '12 at 20:58
  • 1
    @Tessmore: No, it is not possible to do suffix searches with boolean full-text searches (or, for that matter, natural language full-text searches). [The * operator *only* works for prefixes](http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html), as stated in the documentation on that operator. – Michael Madsen Jul 18 '12 at 21:01
  • Alright thanks for pointing that out. I think it is mostly a problem because it is about dutch language :(. I need a way to convert something like "tomatensoep" to "+tomaten +soep" so it can atleast find the soups and perhaps even some soups with tomatoes in them, but alass. Perhaps you know some direction I could look into? (Dutch problem, since English just use spaces in between words!) – Tessmore Jul 18 '12 at 21:40
  • `like` work but it will slower compared to fulltext search – anjaneyulubatta505 Dec 30 '22 at 10:23