209

I want to find an SQL query to find rows where field1 does not contain $x. How can I do this?

Aeon
  • 6,467
  • 5
  • 29
  • 31
zuk1
  • 18,009
  • 21
  • 59
  • 63

3 Answers3

396

What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists:

-- subquery
SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);
-- predefined list
SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

If you are searching a string, go for the LIKE operator (but this will be slow):

-- Finds all rows where a does not contain "text"
SELECT * FROM x WHERE x.a NOT LIKE '%text%';

If you restrict it so that the string you are searching for has to start with the given string, it can use indices (if there is an index on that field) and be reasonably fast:

-- Finds all rows where a does not start with "text"
SELECT * FROM x WHERE x.a NOT LIKE 'text%';
Vegard Larsen
  • 12,827
  • 14
  • 59
  • 102
  • 2
    what is x.b? your letters are very very confusing. I recommend using table or field. – Whitecat Aug 24 '15 at 21:10
  • Be sure that if you use a subquery for `NOT IN` that none of the values will be NULL, as [NOT IN and NULL do not combine in an obvious manner](https://stackoverflow.com/questions/129077/not-in-clause-and-null-values) if you're not familiar with three-valued logic. Here you would use `SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y WHERE b IS NOT NULL);` If you also need to exclude NULL values, you'd need to do this: `SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y WHERE b IS NOT NULL) AND x.b IS NOT NULL;` – Bacon Bits Sep 22 '17 at 17:52
29

SELECT * FROM table WHERE field1 NOT LIKE '%$x%'; (Make sure you escape $x properly beforehand to avoid SQL injection)

Edit: NOT IN does something a bit different - your question isn't totally clear so pick which one to use. LIKE 'xxx%' can use an index. LIKE '%xxx' or LIKE '%xxx%' can't.

Greg
  • 316,276
  • 54
  • 369
  • 333
  • What is considered escaping properly? I know that with normal strings you only have to escape a few things, but LIKE has some extra special characters. – Pieter Bos May 16 '12 at 15:01
-1

Alternative method:

SELECT * FROM x WHERE CHARINDEX('$x', field1, 0)<1

Explanation: It searches for occurrences of '$x' starting from position 0. And only selects where count is 'less than one'.

FearlessCoward
  • 327
  • 3
  • 6