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%';
精彩评论