I'm revisiting this one because i never did find out how to do it!
I want my website search box to be able to find results on single words, multiple words and word phrases.
I have set my ms sql server product table to full text catalog.
Now i ned to put it all together so i have a more intelligent search facility
The select statament i need to amend is:
SELECT dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.ManufacturerID, dbo.Products.Product, dbo.Products.Image, dbo.Products.Price, COUNT(dbo.ClientOffers.ProductID) AS QtyOffers FROM dbo.Products INNER JOIN dbo.ClientProducts ON dbo.Products.ProductID = dbo.ClientProducts.ProductID LEFT OUTER JOIN dbo.ClientOffers ON dbo.Products.ProductID = dbo.ClientOffers.ProductID AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID WHERE (dbo.ClientProducts.ClientID = ?) AND (dbo.Products.Product LIKE ?) GROUP BY dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.Product, dbo.Products.Image, dbo.Products.Price, dbo.Products.ManufacturerID
But i simply don't know how to do it!
My search box simply sends the search word/s to this select statement at the moment.
Given those requirements, you will need something to parse out multiple word/phrase fragments from the search box. To get started, however, you can play around with the CONTAINS function. Here is a sample (assuming the fulltext index is already in place)
where contains (column1, 'product')
The CONTAINS function can get pretty complicated. If you were looking for all forms of the word 'product' (e.g. products), you start having to use functions inside the CONTAINS function:
where contains (column1, 'formsof (inflectional, "product")')