Searching 100000´s of Varchar(max) columns for 1000´s of keywords/sentences

by Peter Tilsted   Last Updated April 09, 2018 13:06 PM

I need some advice/ideas.

I have a table containing job announcements approx. 250,000 records

In another table is 6000 keywords/sentences from applicant cv's historically regarded by the government as typical

I want to find job announcements where the text contains one or more of the keywords

I have been doing this, using full-text-search, but firstly, the variable, canĀ“t hold all keywords and secondly it is very slow

DECLARE  @Keywords NVARCHAR(MAX)

SELECT   @Keywords = STUFF((SELECT '|' + Word
                            FROM  dbo.Keywords
                            FOR XML PATH('') ), 1, 1, '')

SELECT *
FROM   dbo.Notes
WHERE  CONTAINS(Note, @Keywords)

Do you have any bright suggestions of how to solve this challenge ?



Related Questions


SQL FullText Search and pdf

Updated June 29, 2015 13:02 PM


use fulltext formsof inflectional to generate terms

Updated August 11, 2015 19:02 PM

Adding Full Text Search via command line

Updated September 28, 2017 16:06 PM