SQL Query Where Field DOES NOT Contain $x

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating