SQL 不包含Y但包含X的SQL选择字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7664429/
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 string that doesn't contain Y but contains X
提问by Katzumi
I'm attempting to select all of the instances where this database field has a string but does not contain a specific value.
我正在尝试选择此数据库字段具有字符串但不包含特定值的所有实例。
Example: I'm trying to select every instance of "red chair" where "red chair" is not proceeded by "big". If I were to run that query in the following table, I'd only get the row with the ID of 2 back:
示例：我试图选择“红色椅子”的每个实例，其中“红色椅子”不是由“大”进行的。如果我在下表中运行该查询，我只会得到 ID 为 2 的行：
+----------+--------------+---------+ | ID | content | +----------+------------------------+ | 1 | The big red chair | | 2 | I have a red chair | | 3 | I have a big chair | +----------+------------------------+
Thanks in advance!
提前致谢！
采纳答案by kand
You can use:
您可以使用：
LIKE '%red chair%' AND NOT LIKE '%big%'
LIKE '%red chair%' AND NOT LIKE '%big%'
edit: Fixed LIKE matching strings
编辑：固定 LIKE 匹配字符串
回答by Larry Lustig
This will get what you want, assuming the phrase "red chair" occurs only once in content. If it can appear more than once ('The red chair is a big red chair'), what result do you want?
这将得到您想要的结果，假设短语“redchair”在content. 如果它可以出现不止一次（'红色椅子是一把大红色椅子'），你想要什么结果？
SELECT * FROM Furniture
WHERE content LIKE '%red chair%' AND content NOT LIKE '%big red chair%'
回答by Paul Creasey
WHERE content like '%red chair%'
AND content not like '%big red chair%'
It's not going to be fast though!
不过不会很快！
回答by onedaywhen
Select string that STARTS WITH X and DOES NOT CONTAIN X
选择以 X 开头且不包含 X 的字符串
WITH T
AS
(
SELECT *
FROM (
VALUES ('xenophilia'),
('xbox'),
('regex')
) AS T (c)
)
SELECT *
FROM T
WHERE c LIKE 'x%'
AND c NOT LIKE '_%x%';
