SQL: How can I do a keyword search using words stored in a separate search table?

Question

I am doing a keyword search in a SQL table where I want to search for a set of keywords word1, word2, ... , wordn and flag instances where these keywords are found in a new column. Assuming that I am looking for these keywords in a variable [Description] in #TABLE, the query I am using looks like this:

SELECT *
  , (CASE WHEN [Description] LIKE '%word1%' THEN 'word1'
          WHEN [Description] LIKE '%word2%' THEN 'word2'
          ...
          WHEN [Description] LIKE '%wordn%' THEN 'wordn'
          END) as Keywords
INTO #RESULTS_TABLE
FROM #TABLE

Now, the problem with this method is that the keywords I am searching are hard-coded into the code, which makes it inconvenient if I want to alter the set of keywords that I am searching for. Instead of doing this, I would like to have the keywords I will search in some separate table #KEYWORDS as a variable [words] and then reference all the keywords listed in that table (under that variable name) for the search. This would allow me to alter the search table and then re-run the select query on the updated search table, without having to change the select code.

Question: Assuming I have a table #KEYWORDS populated with the keywords I want to search, what is the best way to write the keyword search query so that it gets the keywords from the table rather than from hardcoded terms?

Answer 1

My first choice would be a temp table:

create temporary table words_table (
words varchar
);

insert into words_table values
(word1),
(word2), --  etc for the rest of your rows

Then you can:

select t.[Description]
from
  your_table t
  join words_table wt on
    ON t.[Description] LIKE CONCAT('%',wt.words,'%')

or

select t.[Description]
from
  your_table t
  join words_table wt
    ON t.[Description] LIKE '%' + wt.words +'%'