0

I'm looking to perform a natural language search on several rows of a product database. I'm using natural language to get relevance results. The client has explicitly requested a "Google type search"

The issue i'm having is that many of the products include fractions in their names. e.g. 1/2 inch drill bit.

Unfortunately MySQL treats the "/" character as a word delimiter, so "3/4" is treated as "3" "4" in the match condition, which is the same as it not being there at all as far as the search is concerned.

I've seen other systems that are able to make this work, but I am at a loss.

Any help is greatly appreciated.

Sample query attached at request: SET @matching := "1/4 jobber drill"; SELECT SQL_CALC_FOUND_ROWS *, MATCH( parent_product_category, second_level_product_category, third_level_product_category, fourth_level_product_category, fifth_level_product_category, order_no, short_product_title, long_product_title, manufacturer, brand_name) AGAINST(@matching IN NATURAL LANGUAGE MODE) AS score FROM sb_products WHERE is_promo = "0" AND MATCH( parent_product_category, second_level_product_category, third_level_product_category, fourth_level_product_category, fifth_level_product_category, order_no, short_product_title, long_product_title, manufacturer, brand_name) AGAINST(@matching IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 0, 20; SELECT FOUND_ROWS() as num_rows;

Drew
  • 24,851
  • 10
  • 43
  • 78
Jordon Davidson
  • 159
  • 1
  • 8
  • Please show us the query you're using. – user3741598 Aug 03 '16 at 13:17
  • 1
    May be you are looking for this. http://stackoverflow.com/questions/14926386/how-to-search-for-slash-in-mysql-and-why-escaping-not-required-for-wher – Hamza Zafeer Aug 03 '16 at 13:27
  • No, escaping the / doesn't change anything. – Jordon Davidson Aug 03 '16 at 13:38
  • [There is an answer here](http://stackoverflow.com/questions/21696235/mysql-full-text-period) suggesting that you can edit the list of characters considered word characters in a few files on the server. Not sure if this will do the trick, but it seems close. – JNevill Aug 03 '16 at 14:22
  • Creating a new latin1 collation where the / is mapped as a lowercase letter had no perceivable effect. – Jordon Davidson Aug 03 '16 at 17:47

0 Answers0