SQL Query Where Field DOES NOT Contain $x

Question

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

Answer 1

The IN operator cannot be used with a single field, but you can use this operator in subqueries or with predefined lists as follows:

SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);

-- subquery is used here

SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

-- predefined list is like this

But, if you are looking for the string, then go for the LIKE operator (but this will be slow) like this:

SELECT * FROM x WHERE x.a NOT LIKE '%text%';

-- It'll Find all the rows where a does not contain "text"

And if you don't want the string you are searching for then it has to start with the given string, it can also use indices (if there is an index on that field) and be reasonably fast:

SELECT * FROM x WHERE x.a NOT LIKE 'text%';

--This will find all the rows where a does not start with "text"

To master SQL statements, queries and become proficient in SQL queries, enroll in our industry-recognized SQL training.