Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: full text search question

    Hi all.....
    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:

    Code:
    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.

    I'm coding in classic ASP by the way.

    Very grateful for your help

    Andy

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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)
    Code:
    select *
    from table
    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:
    Code:
    where contains (column1, 'formsof (inflectional, "product")')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •