SQL select string that doesn't contain Y but contains X

Question

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       |        content         | 
+----------+------------------------+
| 1        | The big red chair      |
| 2        | I have a red chair     |
| 3        | I have a big chair     |
+----------+------------------------+

Thanks in advance!

Solution 1

You can use:

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

edit: Fixed LIKE matching strings

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

Solution 3

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

It's not going to be fast though!

Comments

[Rikon]

Or to even be a little more precise: LIKE '%red chair' AND NOT LIKE 'big red%'

[Clockwork-Muse]

Unfortunately, this will still select row 1. It may also have problems with selecting row 2, depending on how the OP's flavor of SQL handles end-of-row blank characters. This is along the right lines - you'll need to add the '%' character on both sides of both strings.

[Larry Lustig]

This answers (incorrectly) the title of the question, but the actual question does not really match the title.

[Katzumi]

Larry, that was my main problem in finding a solution. How would you title this help request? I will attempt to edit, it they allow me.

[Katzumi]

I eventually want to insert the word "small" in front of the instance where RED CHAIR is not preceded by the word big. Therefore, if there is more than one instance in the content - I'd like those replaced too.