Negative substring filtering
Exclude records containing a substring, such as automated accounts or test data.
Drizzle ORM · Function
notLike
notLike() filters out rows whose column value matches a SQL LIKE pattern. The comparison is case-sensitive.
How it works
notLike() creates a type-safe SQL NOT LIKE condition. Pass a column and a pattern string; SQL wildcard characters such as % determine which values are excluded, and matching is case-sensitive according to the documentation.
Syntax
notLike(column, value)Parameters
| Name | Type | Description |
|---|---|---|
column | SQL column | The column whose value is compared with the pattern. |
value | string | A SQL LIKE pattern, commonly using % or _ wildcards. |
Returns
A SQL condition representing a case-sensitive NOT LIKE comparison.
Key points
- Import
notLikefromdrizzle-ormalongside other filter and conditional operators. - It expresses a negative pattern comparison and uses case-sensitive matching.
- Use it in a query filter when rows matching a SQL pattern should be omitted.
Common mistakes
- Matching is case-sensitive; use a different operator when case-insensitive comparison is required.
Example: Exclude names matching a pattern
import { and, notLike } from 'drizzle-orm';
import { users } from './schema';
const result = await db
.select()
.from(users)
.where(notLike(users.name, '%bot%'));Selects users whose names do not contain “bot”, using a case-sensitive SQL pattern comparison.
When to use it
Pattern-based exclusion
Apply SQL wildcard patterns when selecting only values outside a naming convention.
Interview questions
QWhat does notLike() do?
notLike() builds a condition that keeps rows whose column value does not match a supplied SQL pattern. Its comparison is case-sensitive, and the pattern can use SQL wildcards such as %.
Follow-up: How would you exclude values without pattern matching? Use an equality-based negation such as not(eq(column, value)) rather than notLike().
QHow do you use notLike() with a wildcard pattern?
The first argument is the column being tested, and the second is the pattern to compare against. For example, notLike(users.name, '%bot%') excludes names containing bot.
Follow-up: What does % mean in the example? It matches any sequence of characters, so the pattern checks for bot anywhere in the value.
QWhat case-sensitivity behavior should you expect?
The operator performs a case-sensitive comparison. Therefore, values differing only by letter case may not be excluded unless the database or pattern comparison treats them as equal.
Follow-up: Which documented operator is the case-insensitive counterpart? notIlike() is the corresponding case-insensitive negative pattern operator where the dialect supports it.
Related
likenotIlikeilikeandnot