Introduction

Filtering rows where a text column does not contain a substring looks simple, but the exact answer depends on null handling, collation, and scale. A query that seems correct on a tiny dataset can produce surprising results in production if you do not define those details explicitly.

Start with NOT LIKE

For ordinary substring exclusion, the usual SQL pattern is NOT LIKE with wildcards on both sides of the search term.

SELECT id, name
FROM products
WHERE name NOT LIKE '%x%';

This means "return rows whose name does not contain x anywhere." The % wildcard matches any sequence of characters, including an empty sequence.

If you want a prefix or suffix rule instead of a general substring rule, change the pattern shape:

1-- does not start with x
2WHERE name NOT LIKE 'x%'
3
4-- does not end with x
5WHERE name NOT LIKE '%x'

The pattern matters because %x% prevents the database from using many normal index optimizations, while a suffix-free prefix check may still be easier to optimize depending on the engine.

Handle Nulls, Case, and Escaping

A frequent mistake is assuming that NOT LIKE includes null values. It does not. In SQL's three-valued logic, NULL NOT LIKE '%x%' is still unknown, not true.

If null should count as "does not contain," include it explicitly:

1SELECT id, name
2FROM products
3WHERE name IS NULL
4   OR name NOT LIKE '%x%';

Case sensitivity is another source of surprises. Depending on collation, x, X, and accented variants may or may not compare the same way. A portable but sometimes slower approach is to normalize both sides.

1SELECT id, name
2FROM products
3WHERE name IS NULL
4   OR LOWER(name) NOT LIKE '%x%';

If the search term may contain wildcard characters such as % or _, escape them before placing them in a LIKE predicate.

SELECT id, code
FROM items
WHERE code NOT LIKE '%\_%' ESCAPE '\\';

In that example, the underscore is treated as a literal character instead of a single-character wildcard.

Parameterize User Input Safely

In application code, never build the pattern by concatenating raw user input directly into SQL. Use parameter binding and construct the wildcard pattern safely.

1SELECT id, name
2FROM products
3WHERE name IS NULL
4   OR name NOT LIKE CONCAT('%', ?, '%');

The exact placeholder syntax varies by driver and database, but the principle is the same: bind the value, do not paste it into the SQL string.

You should also decide whether the user input is a literal substring or a pattern language. If it is literal input, escape % and _ before searching.

When NOT EXISTS or Regex Is a Better Fit

Sometimes the requirement sounds like a text exclusion problem but is really a relationship exclusion problem. In that case, NOT EXISTS is the better tool.

1SELECT c.customer_id, c.name
2FROM customers c
3WHERE NOT EXISTS (
4  SELECT 1
5  FROM support_tickets t
6  WHERE t.customer_id = c.customer_id
7    AND t.status = 'OPEN'
8);

That query excludes customers with open tickets. Trying to model the same idea through text matching would be both fragile and slow.

Regex operators can also help when the exclusion rule is more complex than a simple substring. Some databases support REGEXP, SIMILAR TO, or vendor-specific regex functions. Use those only when pattern complexity justifies the extra cognitive and performance cost.

Performance Considerations

Leading-wildcard patterns such as %x% often force a scan because the engine cannot use a normal B-tree index efficiently. If this query matters at scale, consider a full-text index, a trigram index, a computed search column, or a search service designed for substring queries.

The right optimization depends on your database engine, but the key idea is universal: substring exclusion on large tables needs an intentional search strategy.

Common Pitfalls

Forgetting that null does not satisfy NOT LIKE is one of the most common correctness bugs. Add IS NULL when that behavior is desired.

Assuming case-insensitive matching everywhere is risky because collation rules differ across systems.

Using %term% on very large tables without a text-search strategy can create slow queries and unstable plans.

Summary

• Use NOT LIKE '%x%' for the basic "does not contain" case.
• Handle null values explicitly if they should be included.
• Decide case sensitivity and wildcard escaping up front rather than relying on defaults.
• Use NOT EXISTS for relational exclusion and regex only when the pattern actually requires it.