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

    Unanswered: Full text search and rank question

    Hi all...
    I'm using MS SQL Server Advanced Features.
    I've applied Full Text Index to my products table (e-commerce site).
    I'm using this select statement as a test for my product search box.

    Code:
    SELECT * FROM Products join ContainsTable(Products, product, '"search product"') as FT
    on FT.[key]=ProductID
    Order By RANK ASC
    The results i get arn't really what i was expecting.
    If i type in a search such as cb300 i get the correct product returned, cb300 professional checkbox.
    However, if i type in cb300 checkbox (missing out the word professional), i don't get anything returned.
    It seems i only get the correct return if the search string is in a logical order.

    I'm using MS SQL 2008 and Classic ASP.

    Any ideas on how to make the search more intelligent?

    Andy

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    A look at the containstable and contains would have helped you:

    Code:
    SELECT * FROM Products join ContainsTable(Products, product, 'cb300 and checkbox') as FT
    on FT.[key]=ProductID
    Order By RANK ASC
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Thanks roac
    I'm working through the sample code below and replacing my database details for table, column_name, etc...
    Do you have and real life samples of this code to help me through it please?
    Andy


    Code:
    CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ' < contains_search_condition > ' 
         [ , LANGUAGE language_term] 
      [ , top_n_by_rank ] 
              ) 
    < contains_search_condition > ::= 
        { < simple_term > 
        | < prefix_term > 
        | < generation_term > 
        | < proximity_term > 
        |  < weighted_term > 
        } 
        | { ( < contains_search_condition > ) 
        { { AND | & } | { AND NOT | &! } | { OR | | } } 
         < contains_search_condition > [ ...n ] 
        }
    < simple_term > ::= 
         word | " phrase "
    < prefix term > ::= 
         { "word * " | "phrase *" } 
    < generation_term > ::= 
         FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) 
    < proximity_term > ::= 
         { < simple_term > | < prefix_term > } 
         { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] 
    < weighted_term > ::= 
         ISABOUT
        ( { { 
      < simple_term > 
      | < prefix_term > 
      | < generation_term > 
      | < proximity_term > 
      } 
       [ WEIGHT ( weight_value ) ] 
       } [ ,...n ] 
        )

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    If you look at the very bottom of Contains, there are several of them, and I even presented a piece of code in my previous post which should solve the issue you presented in your original post. What is it that you don't get to work?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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