SQL Full Text Index Contains

Question

I am fairly new to SQL FTI and I am trying to perfect a search against a table with millions of different product items.

If I pass a search such as this:

select top 100 * from OMG_ProductFeeds.dbo.tbl_products
 where CONTAINS(ProductName,'"apple iphone 6"')

I get these results back:

Acm Rich Leather Soft Carry Case For Apple Iphone 6 Mobile Handpouch Holder Cover - Black
Apple iPhone 6 - 16 GB
Apple iPhone 6 Plus - 64 GB
Apple iPhone 6 - 64 GB
Apple iPhone 6 Plus - 16 GB
Chevron Set Of 3 Ultra Clear Screen Guard + 3 Matte Finish Screen Guard For Apple Iphone 6 - Combo Offer
Softy Back Cover Case For Apple Iphone 6 - Golden
Chevron Ultra Clear Hd Finish Screen Guard Protector For Apple Iphone 6 (pack Of 5)

I want to be able to include actual iphone 6 phones but ignore any of the peripherals such as cases and screen protectors but I am not sure how to do this.

Any advice is appreciated.

Thanks

This is impossible. You should have type column in your table and filter on it — Giorgi Nakeuri, Apr 19 '15 at 06:37

score 2 · Accepted Answer · edited Apr 19 '15 at 07:26

2

You may consider re-designing your DB structure with a extra table - tbl_product_categories.

In tbl_products we can add a foreign key column to reference the corresponding category from tbl_product_categories. So that you can filter only certain category with certain keyword.

SELECT TOP 100 *
FROM OMG_PRODUCTFEEDS.DBO.TBL_PRODUCTS
WHERE CONTAINS(PRODUCTNAME,'"APPLE IPHONE 6"')
AND product_category_id = 1;

edited Apr 19 '15 at 07:26

sqluser

5,502
7
36
50

answered Apr 19 '15 at 06:42

Chris Lam

3,526
13
10

Thanks, that's a fair comment. There is a category column that can be used for this but firstly the data is form an external source and not always reliable. Secondly, the search term will be from user input and I won't necessarily have the luxury of passing a category. – Giles Hunt Apr 19 '15 at 06:59
If all the actual iPhone products got the same pattern in their names, using REGEXP should be the way to go. http://stackoverflow.com/questions/3452572/mysql-regex-in-the-where-clause – Chris Lam Apr 19 '15 at 07:04
Sample regexp you may try out: `^Apple iPhone \d+( Plus)? - \d+ GB$` – Chris Lam Apr 19 '15 at 07:07

SQL Full Text Index Contains

1 Answers1