I want to find an SQL query to find rows where field1 does not contain $x. How can I do this?
Add a comment |
3 Answers
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%'; 
2 Comments
Whitecat
what is x.b? your letters are very very confusing. I recommend using table or field.
Bacon Bits
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 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;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.
1 Comment
Pieter Bos
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.
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'.
