SQL SELECT WHERE field contains words
I need a select which would return results like this:
SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'
And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' or any other combination of the three.
All words need to be in the result.
Rather slow, but working method to include any of words:
SELECT * FROM mytable WHERE column1 LIKE '%word1%' OR column1 LIKE '%word2%' OR column1 LIKE '%word3%'
If you need all words to be present, use this:
SELECT * FROM mytable WHERE 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.
Read more... Read less...
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 MyTable WHERE 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.
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 )
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
SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3',
add And in between those words like:
SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 And word2 And word3'
for details, see here https://msdn.microsoft.com/en-us/library/ms187787.aspx
For selecting phrases, use double quotes like:
SELECT * FROM MyTable WHERE Column1 CONTAINS '"Phrase one" And word2 And "Phrase Two"'
p.s. you have to first enable Full Text Search on the table before using contains keyword. for more details, See here https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search
SELECT * FROM MyTable WHERE Column1 LIKE '%word1%' AND Column1 LIKE '%word2%' AND Column1 LIKE '%word3%'
AND based on edit to question.
If you are using Oracle Database then you can achieve this using contains query. Contains querys are faster than like query.
If you need all of the words
SELECT * FROM MyTable WHERE CONTAINS(Column1,'word1 and word2 and word3', 1) > 0
If you need any of the words
SELECT * FROM MyTable WHERE CONTAINS(Column1,'word1 or word2 or word3', 1) > 0
Contains need index of type CONTEXT on your column.
CREATE INDEX SEARCH_IDX ON MyTable(Column) INDEXTYPE IS CTXSYS.CONTEXT