0
SELECT DESCP, MATCH (DESCP, NAME) AGAINST ('slice' IN BOOLEAN MODE ) AS Score FROM products WHERE MATCH (DESCP, NAME) AGAINST ('slice')

Above is the query I have as an example.

The results I get are:

  • Slice Decorated Chocolate Cake, Chocolatec 1
  • Cake Slice 1

  • Kraft Natural Big Slice Aged Swiss Cheese 1

    SELECT DESCP, MATCH (DESCP, NAME) AGAINST ('slices' IN BOOLEAN MODE ) AS Score FROM products WHERE MATCH (DESCP, NAME) AGAINST ('slices')

This query results in:

  • CHEESE SLICES 1
  • Kraft Natural Sliced Extra Thin Swiss Cheese - 8 o 1
  • Sargento Ultra Thin Sliced Baby Swiss Cheese - 18 1
  • Sargento Natural Deli Style Sliced Swiss Cheese - 1
  • Kraft Natural Big Slice Aged Swiss Cheese - 8 oz 1
  • Sargento Sliced Muenster Natural Cheese 8 oz. 1
  • Kraft Sliced Deli Deluxe American Cheese 16 Slices... 1

So my question is:

How do I match a fulltext match that will find all the words for slice, sliced, slices, etc. I am taking user input from a text box and the expectation is that if someone searched for "slice" or "slices" that they get all the correct results.

Bicycle
  • 63
  • 9
  • This function you're looking for is called "stemming" in the world of lex search. – O. Jones Jan 04 '16 at 17:28
  • Thanks now I know the term for it. The link you provided eventually leads to a bunch of 404s so it doesn't really help as much to anyone looking for a similar answer. – Bicycle Jan 04 '16 at 18:23

1 Answers1

0

use LIKE

SELECT * from table WHERE field LIKE '%slice%'

% matches any prefix/suffix

John Pangilinan
  • 953
  • 1
  • 8
  • 25
  • So If someone types in cheese slice SELECT * from table WHERE field LIKE '%cheese slice%' will match Kraft Natural Sliced Extra Thin Swiss Cheese? – Bicycle Jan 04 '16 at 16:12
  • anything with 'cheese slice' in there content will be included, include `cheese slices` or `anytextcheese sliceanytext` – John Pangilinan Jan 04 '16 at 16:14
  • I disagree in that it won't return the desired results and that I am reading fulltext is more efficient. If a person types "Cheese slice" and the record is "Sliced cheese" %cheese slice% will not result in anything. Plus it would need to be %cheese%slice% to give any sort of result at all. And then the stop words will not be omitted. – Bicycle Jan 04 '16 at 16:18
  • You can `explode` the users search text by spaces then concatinate a `OR LIKE` for each entry. `LIKE '%cheese slices%' OR '%slices%' OR '%cheese%'` – John Pangilinan Jan 04 '16 at 16:21