CODEWITHSUNDEEP
sql-serverfull-text-search
cnom
cnom

Reputation: 3241

Sql server full text search for a list of words

I am struggling with the expression syntax of sql server full-text-search feature.

I have a list of user "words" that need to be found in a column of my table:

SELECT DocumentNode, DocumentSummary  
FROM Production.Document  
WHERE CONTAINS(DocumentSummary, '"word1" OR "word2"')

Is there an efficiect way to do this for a list of x-number words? Something better than:

'"word1" OR "word2" ... OR "wordx"'

UPDATE: Let me clarify that my table (for the example here - Production.Document) has several milions of records, so I need the query to have the best performance possible. Also the query might execute some thousands of times per day. I am writting this to explain why the LIKE queries is not an option.

PS: If there is any alternative approach I am missing (other than full-text-search), please do tell.

Upvotes: 0

Views: 3496

Answers (2)

Ali
Ali

Reputation: 1286

There is no way to do it with CONTAINS as CONTAINS only takes a column as the first parameter and not the second. However, you could put your words in a table and then use dynamic sql to execute your query:

Declare @WordTable table
     (Id int primary key,
      Word varchar(25))

Insert @WordTable values (1, 'word1')
Insert @WordTable values (2, 'word2')
Insert @WordTable values (3, 'word3')

DECLARE @Words VARCHAR(MAX)

SELECT @Words = STUFF ((
        SELECT '"' + Word + '" OR '
        FROM @WordTable
        FOR XML PATH('')
    ), 1, 0, '')

--drop the last OR
  SELECT  @Words = SUBSTRING(@Words, 0, LEN(@Words) - 2)

  DECLARE @Query VARCHAR(MAX)
  SELECT  @Query = 'SELECT DocumentNode, DocumentSummary  
                    FROM Production.Document  
                    WHERE CONTAINS(DocumentSummary, ''' + @Words +''')'

  EXEC(@Query)

Upvotes: 1

yemo
yemo

Reputation: 155

If you need to find all the words in list, use 'LIKE' operator like this: 

SELECT Document.Node, Document.Summary
FROM Production.Document
WHERE Document.Summary LIKE '%word1%'
  AND Document.Summary LIKE '%word2%'

On the other hand, if you need to look for any word in list just change logical operation to OR. Hope, it will help :)

Upvotes: 0

Related Questions