Filter rows by whether a text column contains any words in a string in SQL

My SQL Server database table has a column text which is a long string of text.

The search list is a string of words separated by comma. I want to grab those rows where the text column contains any one of words in the string.

DECLARE @words_to_search nvarchar(50)
SET @words_to_search = 'apple, pear, orange' 

SELECT * 
FROM myTbl
WHERE text ??? --how to specify text contains @words_to_search  

Thanks a lot in advance.

Answer

If you’re running SQL Server 2016 or later, you can use STRING_SPLIT to convert the words to search into a single column table, and then JOIN that to your table using LIKE:

DECLARE @words_to_search nvarchar(50)
SET @words_to_search = 'apple,pear,orange'

SELECT *
FROM myTbl
JOIN STRING_SPLIT(@words_to_search, ',') ON text LIKE '%' + value + '%';

Demo on SQLFiddle

Note that as the query is written it will (for example) match apple within Snapple. You can work around that by making the JOIN condition a bit more complex:

SELECT *
FROM myTbl t
JOIN STRING_SPLIT(@words_to_search, ',') v
  ON t.text LIKE '%[^A-Za-z]' + value + '[^A-Za-z]%'
  OR t.text LIKE value + '[^A-Za-z]%'
  OR t.text LIKE '%[^A-Za-z]' + value;

Demo on SQLFiddle