SQL SELECT WHERE字段包含单词

本文翻译自：SQL SELECT WHERE field contains words

I need a select which would return results like this: 我需要一个select会返回如下结果：

SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'

And I need all results, ie this includes strings with 'word2 word3 word1' or 'word1 word3 word2' or any other combination of the three. 我需要所有结果，即这包括带有'word2 word3 word1'或'word1 word3 word2'的字符串或三者的任何其他组合。

All words need to be in the result. 所有单词都需要在结果中。

---

#1楼

参考：https://stackoom.com/question/xxhh/SQL-SELECT-WHERE字段包含单词

---

#2楼

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. 如果你想要更快的东西，你需要查看全文搜索，这对每种数据库类型都是非常具体的。

---

#3楼

SELECT * FROM MyTable WHERE 
Column1 LIKE '%word1%'
AND Column1 LIKE '%word2%'
AND Column1 LIKE  '%word3%'

Changed OR to AND based on edit to question. 根据编辑问题将OR更改为AND 。

---

#4楼

select * from table where name regexp '^word[1-3]$'

要么

select * from table where name in ('word1','word2','word3')

---

#5楼

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. 请注意，如果使用LIKE确定字符串是否是另一个字符串的子字符串，则必须转义搜索字符串中的模式匹配字符。

If your SQL dialect supports CHARINDEX , it's a lot easier to use it instead: 如果您的SQL方言支持CHARINDEX ，则使用它会更容易：

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. 因此，例如，字符串'word1word2word3'仍将匹配。

---

#6楼

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