2

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column.

I want to populate a table results with a record for each phrase, and the number of times that phrases was found in the description:

INSERT INTO results(phrase, foundcount)
SELECT  phrase, 
    (SELECT COUNT(*) FROM table WHERE CONTAINS(table.description, phrase))
FROM    phrases;

No matter how I try, this results in the error:

Incorrect syntax near `phrase`. Expecting STRING, TEX_LEX, or VARIABLE

I would really rather not use a cursor just so I can assign each phrase to a variable. Is that really the only option?

1

Try this:

go
create function dbo.fn_GetCountForPhrase (@phrase varchar(500))
    returns  int
as
begin
    declare @count int
    declare @localphrase varchar(8000)
    set @localphrase='"' + @phrase + '"'
    SELECT @count=COUNT(*) FROM table  a WHERE CONTAINS(table.description,   @localphrase)
    return @count
end
go
SELECT  
    phrase,  
    dbo.fn_GetCountForPhrase(phrase) 
FROM phrases 
0

I think you can get what you need from the DMVs, including sys.dm_fts_index_keywords_by_document and sys.dm_fts_parser, something like this:

SELECT p.phrase, kbd.display_term
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') ) kbd
    CROSS APPLY dbo.phrases p
        CROSS APPLY sys.dm_fts_parser  ( N'FORMSOF(FREETEXT, "' + phrase + N'")', 1033, 0, 1 ) x
WHERE x.display_term = kbd.display_term

Caution: if you wrap your searches in quotes you get an exact match, so you're not really getting the benefit of full-text indexing. You could also just do that with Like and wildcards(%). Also scalar functions do not tend to scale well over large resultsets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.