3

I currently have the following select statement, but I wish to move to full text search on the Keywords column. How would I re-write this to use CONTAINS?

SELECT MediaID, 50 AS Weighting
FROM Media m JOIN @words w ON m.Keywords LIKE '%' + w.Word + '%'

@words is a table variable filled with words I wish to look for:

DECLARE @words TABLE(Word NVARCHAR(512) NOT NULL);
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
Sprintstar
  • 7,938
  • 5
  • 38
  • 51

2 Answers2

4

If you are not against using a temp table, and EXEC (and I realize that is a big if), you could do the following:

DECLARE @KeywordList VARCHAR(MAX), @KeywordQuery VARCHAR(MAX)
SELECT @KeywordList = STUFF ((
        SELECT '"' + Keyword + '" OR '
        FROM FTS_Keywords
        FOR XML PATH('')
    ), 1, 0, '')

SELECT  @KeywordList = SUBSTRING(@KeywordList, 0, LEN(@KeywordList) - 2)
SELECT  @KeywordQuery = 'SELECT RecordID, Document FROM FTS_Demo_2 WHERE CONTAINS(Document, ''' + @KeywordList +''')'

--SELECT @KeywordList, @KeywordQuery

CREATE TABLE #Results (RecordID INT, Document NVARCHAR(MAX))

INSERT INTO #Results (RecordID, Document)
EXEC(@KeywordQuery)

SELECT * FROM #Results

DROP TABLE #Results

This would generate a query like:

SELECT   RecordID
        ,Document 
FROM    FTS_Demo_2 
WHERE CONTAINS(Document, '"red" OR "green" OR "blue"')

And results like this:

RecordID    Document
1   one two blue
2   three red five
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
  • `The character string that starts with '..***..' is too long. Maximum length is 4000.` But anyway thanks, this do the work I got just too much data. +1 – krtek Mar 16 '17 at 09:52
2

If CONTAINS allows a variable or column, you could have used something like this.

SELECT MediaID, 50 AS Weighting
FROM Media m
JOIN @words w ON CONTAINS(m.Keywords, w.word)

However, according to Books Online for SQL Server CONTAINS, it is not supported. Therefore, no there is no way to do it.

Ref: (column_name appears only in the first param to CONTAINS)

CONTAINS
( { column_name | ( column_list ) | * } 
  ,'<contains_search_condition>'     
[ , LANGUAGE language_term ]
)
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    Thanks for your answer, as a work-around I will restructure my code to pass my list of words to the CONTAINS function directly, delimited with 'OR'. – Sprintstar Mar 01 '11 at 11:31