Full Text Search using a column that contains a list of keywords

Apparently I am the first person on the Internet to need to do this, which I find hard to believe, but I couldn’t find anything about this, so I had to make a solution myself. The problem I was trying to solve was this: I have a table with a column, and I want to search that column for keywords.

No problem, that’s what Full Text Indexes do. However, I didn’t want to just search for one static word, or a list of static words. I wanted to search for a list of keywords that I was storing in a column in a different table.

I could not find any way to dynamically pass a to a Full Text Query results from another query, which would have been really easy. Instead, I had to create a list, enclosed in double quotes (“”) and separated by commas (,) from my table containing my keyword list. I made this list into a variable and then passed that variable to the “CONTAINS” query I wrote. Viola! It worked.

This is the sample code I used:

DECLARE @TermList varchar(100)

SELECT @TermList = COALESCE(@TermList + '", ', '') + '"' +

 term

FROM dbo.term

select * from dbo.calls where

FREETEXT ([Subject],@TermList)

 

Where @TermList is the variable I created to hold my list of terms, the “term” column in dbo.term is the column containing all my keywords, and the dbo.calls table has the “subject” column that I want to look in to find matches.

Hope this helps somebody!

Reed