开发者

SQL select string that doesn't contain Y but contains 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 c开发者_如何学运维hair" 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       |        content         | 
+----------+------------------------+
| 1        | The big red chair      |
| 2        | I have a red chair     |
| 3        | I have a big chair     |
+----------+------------------------+

Thanks in advance!


You can use:

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

edit: Fixed LIKE matching strings


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%'


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

It's not going to be fast though!


Select string that STARTS WITH X and DOES NOT CONTAIN 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%';
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜