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!

You wouldn't get row 3 back either since it doesn't contain the text "RED CHAIR". — Justin Helgerson, Oct 05 '11 at 16:19
Some engines are case insensitive by default, others have options (or operators) to perform this comparison in a case-insensitive manor. In any event, I doubt it's the case-sensitivity that Katzumi is wrestling with. — Larry Lustig, Oct 05 '11 at 16:25
-1 Title and question are contradictory. Also, example and text are contradictory (upper case?). Please fix. — Erwin Brandstetter, Oct 05 '11 at 16:29
Updated the question title to match the response and removed the upper case. :) — Katzumi, Feb 12 '14 at 17:01

kand · Accepted Answer · 2011-10-05T16:29:52.980

15

You can use:

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

edit: Fixed LIKE matching strings

edited Oct 05 '11 at 16:29

answered Oct 05 '11 at 16:18

kand

2,268
6
31
43

Or to even be a little more precise: LIKE '%red chair' AND NOT LIKE 'big red%' – Rikon Oct 05 '11 at 16:20
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. – Clockwork-Muse Oct 05 '11 at 16:22
1

This answers (incorrectly) the title of the question, but the actual question does not really match the title. – Larry Lustig Oct 05 '11 at 16:24
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 Oct 05 '11 at 16:27

score 4 · Answer 2 · answered Oct 05 '11 at 16:20

4

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

answered Oct 05 '11 at 16:20

Larry Lustig

49,320
14
110
160

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. – Katzumi Oct 05 '11 at 16:29

score 3 · Answer 3 · answered Oct 05 '11 at 16:19

3

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

It's not going to be fast though!

answered Oct 05 '11 at 16:19

Paul Creasey

28,321
10
54
90

score 0 · Answer 4 · answered Oct 06 '11 at 09:50

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

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

4 Answers4