# PostgreSQL LIKE treats underscore as a wildcard

> In PostgreSQL LIKE, underscore matches any one character. Learn how an innocent prefix filter can exclude every row and how to escape it safely.

Source: https://saascode.ai/inside/sql-like-underscore · Published: 2026-08-21 · Section: academy

---
You write `NOT LIKE '__%'` to exclude names beginning with two underscores. The query runs, returns no error, and quietly excludes almost every name with at least two characters.

That happened in a schema-introspection query. It filtered all 49 application tables, and the fallout took 8 migrations to unwind.

The bug is standard SQL behavior: inside a `LIKE` pattern, `_` matches exactly one character and `%` matches any sequence. So `__%` means “any two characters, followed by anything.” It does not mean a literal double-underscore prefix.

## Pick an escape character you can read

PostgreSQL lets you declare the escape character explicitly. Using `!` avoids the layers of backslash escaping that appear when SQL is embedded in another language:

```sql
select table_name
from information_schema.tables
where table_name not like '!_!_%' escape '!';
```

Here, each `!_` means a literal underscore. The final `%` remains a wildcard, so the pattern now means “starts with `__`.”

You can use a backslash instead, but make the `ESCAPE` clause explicit when clarity matters. The exact representation may otherwise become hard to review across SQL, JavaScript strings, migration tooling, and shell quoting.

## Parameters do not remove pattern semantics

A parameterized query protects the SQL grammar, but the bound value is still interpreted as a `LIKE` pattern. If a user searches for `50%_off`, the percent sign and underscore remain wildcards unless your product intends them to be.

Escape the chosen escape character first, then `%` and `_`:

```ts
function escapeLike(value: string) {
  return value
    .replaceAll('!', '!!')
    .replaceAll('%', '!%')
    .replaceAll('_', '!_')
}
```

The SQL using that value must declare `ESCAPE '!'`. Keep the escaping helper and the SQL convention together; changing one without the other creates a new bug.

## Review every identifier pattern

Snake case makes this especially easy to miss. A pattern such as `user_%` may mean “the literal prefix `user_`,” or it may intentionally accept any character after `user`. A reviewer cannot tell unless the underscore is escaped or the intent is documented.

Search migrations and query builders for `LIKE` and `ILIKE`. For every `%` and `_`, ask whether it is data or syntax. Then test a positive case, a near miss, and a short string. The original query passed code review because it looked like English. The database read it as a pattern language.