SQL选择不包含Y但包含X的字符串

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!

提前致谢！

4 个解决方案

#1

You can use:

您可以使用：

LIKE '%red chair%' AND NOT LIKE '%big%'

喜欢'％red chair％'并且不喜欢'％big％'

edit: Fixed LIKE matching strings

编辑：修复了LIKE匹配的字符串

#2

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?

这将得到你想要的，假设短语“红色椅子”只在内容中出现一次。如果它可以出现不止一次（'红色椅子是一把大红色椅子'），你想要什么结果？

SELECT * FROM Furniture 
  WHERE content LIKE '%red chair%' AND content NOT LIKE '%big red chair%'

#3

WHERE content like '%red chair%'
AND content not like '%big red chair%'

It's not going to be fast though!

虽然它不会很快！

#4

Select string that STARTS WITH X and DOES NOT CONTAIN X

选择STARTS WITH 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%';

#1