Post reply

possible to return the amount of keywords found using function contains

  • September 29, 2010 at 5:53 am

    #228854

    I am fairly new with full text search. I managed to put the contains

    search right. But is it possible to return the amount of keywords

    found?

    For example, if the keyword searching is "law", both rows:

    1. law and order

    2. family law and Chinese law

    are found.

    But how can I return the amount of keyword found (except for having a

    function to search for the keyword manually)?

  • September 29, 2010 at 8:48 am

    #1229103

    This works on Adventure Works when I want to find all occurences of the keyword 'product' in my JobCandidate table :

    [font="Courier New"]SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2008'),

    object_id('HumanResources.JobCandidate'))

    where display_term = 'product';[/font]

    One of the fields in the result set: Occurence_Count will give you the # of occurences in each document where a "document" corresponds to a row in your table.

    So if you use this :

    [font="Courier New"]SELECT SUM(kbd.occurrence_count) FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2008'), object_id('HumanResources.JobCandidate')) kbd

    where display_term = 'product';[/font]

    that should give you the TOTAL number of occurences

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply