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