How to search for a list of words contains in a string column?

To search whether the list of words contains in a column, you can implement it using a series of conditions with LIKE and OR operators. But to search for additional word, you need to add another condition.

SELECT * FROM dbo.my_table 
WHERE description LIKE '%word1%'
OR description LIKE '%word2%'
OR description LIKE '%word3%'

Instead of the above approach, you can create the below function dbo.fn_contains that search for the words in a string column. This function uses another function dbo.fn_string_to_list which converts a string to a list of values.

CREATE OR ALTER FUNCTION dbo.fn_contains(
    @string_to_search NVARCHAR(4000)
  , @string_to_find NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN

DECLARE @word_count INT;
DECLARE @word NVARCHAR(4000);
DECLARE @i INT = 1;

DECLARE @split_table TABLE (
	id INT IDENTITY(1,1),
	word NVARCHAR(4000)
)

INSERT INTO @split_table
SELECT word FROM dbo.fn_string_to_list (@string_to_find,' ');

SET @word_count = (SELECT COUNT(1) FROM @split_table)

WHILE @word_count >= @i
BEGIN
	SET @word = (SELECT word FROM @split_table WHERE id = @i)
	IF @string_to_search LIKE '%'+@word+'%' RETURN 1;
	SET @i += 1;
END

RETURN NULL
END

GO

Sample query to search for a list of words in a string column.

SELECT description 
FROM dbo.myTable 
WHERE dbo.fn_contains(description,'word1 word2 word3') = 1
GO 