Search code examples
sqlsql-serverfull-text-indexingcontainstable

SQL Full Text Index Contains

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

Solution

  • 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;