SQL SELECT LIKE 仅包含特定单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14291060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT LIKE containing only specific words
提问by Mario M
I have this query:
我有这个查询：
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'
I need to modify this query to return records for which column1 contains word1 word2 and word3 and nothing else! no other words, just these words.
我需要修改此查询以返回 column1 包含 word1 word2 和 word3 而没有其他记录的记录！没有别的词，只有这些词。
Example: searching for samsung galaxy s3should return any combination of samsung s3 galaxybut NOT samsung galaxy s3 lte
示例：搜索samsung galaxy s3应该返回samsung s3 galaxy但 NOT 的任意组合samsung galaxy s3 lte
回答by Damien_The_Unbeliever
Assumingthat column1contains space separated words, and you only want to match on wholewords, something like:
假设这column1包含空格分隔的单词，而你只想要匹配全词，是这样的：
SELECT * FROM
(select ' ' + REPLACE(column1,' ',' ') + ' ' as column1 from mytable) t
WHERE
column1 like '% word1 %' AND
column1 like '% word2 %' AND
column1 like '% word3 %' AND
REPLACE(REPLACE(REPLACE(column1,
' word1 ',''),
' word2 ',''),
' word3 ','') = ''
Note that this construction does allow the sameword to appear multiple times. It's not clear from the question whether that should be allowed. (Fiddle)
请注意，这种结构确实允许同一个词出现多次。从是否应该允许的问题中不清楚。（小提琴）
It would be a far better design if these words were stored as separate rows in a separate table that relates back to mytable. We could then use more normal SQL to satisfy this query. Your example looks like it's some kind of tagging example. Having a table storing each tag as a separate row (with an ordinal position also recorded, if required) would turn this into a simple relational division problem.
如果将这些单词作为单独的行存储在与mytable. 然后我们可以使用更普通的 SQL 来满足这个查询。您的示例看起来像是某种标记示例。将每个标签存储为单独的行（如果需要，还记录一个序号位置）的表会将其变成一个简单的关系划分问题。
A way to count how many times a word appears in a column is the expression:
计算一个词在列中出现的次数的一种方法是表达式：
(LEN(column2) - LEN(REPLACE(column2,'word',''))/LEN('word')
but this would again revert back to matching subsequences of larger words as well as the word itself, without more work.
但这将再次恢复到匹配较大词的子序列以及词本身，无需更多工作。
回答by Code Spy
Try This
尝试这个
SELECT * FROM mytable
WHERE column1 LIKE 'word1'
AND column1 LIKE 'word2'
AND column1 LIKE 'word3'
回答by vidyadhar
in MySQLyou can use regexp as
在MySQL你可以使用正则表达式作为
SELECT * FROM mytable
WHERE column1 regexp '^word[1-3]$';
in postgresyou can use 'similar to' key word
在postgres你可以使用' similar to'关键字
i think oracle also has regexp
我认为oracle也有 regexp
