Rather slow, but working method to include any of words:

SELECT * FROM mytableWHERE column1 LIKE '%word1%'   OR column1 LIKE '%word2%'   OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytableWHERE column1 LIKE '%word1%'  AND column1 LIKE '%word2%'  AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

Note that if you use LIKE to determine if a string is a substring of another string, you must escape the pattern matching characters in your search string.

If your SQL dialect supports CHARINDEX, it's a lot easier to use it instead:

SELECT * FROM MyTableWHERE CHARINDEX('word1', Column1) > 0  AND CHARINDEX('word2', Column1) > 0  AND CHARINDEX('word3', Column1) > 0

Also, please keep in mind that this and the method in the accepted answer only cover substring matching rather than word matching. So, for example, the string 'word1word2word3' would still match.

Function

 CREATE FUNCTION [dbo].[fnSplit] ( @sep CHAR(1), @str VARCHAR(512) ) RETURNS TABLE AS RETURN (           WITH Pieces(pn, start, stop) AS (           SELECT 1, 1, CHARINDEX(@sep, @str)           UNION ALL           SELECT pn + 1, stop + 1, CHARINDEX(@sep, @str, stop + 1)           FROM Pieces           WHERE stop > 0      )      SELECT           pn AS Id,           SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop - start ELSE 512 END) AS Data      FROM           Pieces )

Query

 DECLARE @FilterTable TABLE (Data VARCHAR(512)) INSERT INTO @FilterTable (Data) SELECT DISTINCT S.Data FROM fnSplit(' ', 'word1 word2 word3') S -- Contains words SELECT DISTINCT      T.* FROM      MyTable T      INNER JOIN @FilterTable F1 ON T.Column1 LIKE '%' + F1.Data + '%'      LEFT JOIN @FilterTable F2 ON T.Column1 NOT LIKE '%' + F2.Data + '%' WHERE      F2.Data IS NULL