SQL LIKE % vs. _: Search for a Literal Underscore with ESCAPE

You use LIKE 'task_%' to find codes beginning with task_, but taskA01 appears too. The data may be fine; first inspect the underscore in the pattern. In SQL LIKE, _ does not mean a literal underscore. It matches any single character.

Why does taskA01 match LIKE task_%?

Build a short list of codes. You want task_01 but not taskA01:

CREATE TABLE tasks (code TEXT NOT NULL);

INSERT INTO tasks (code) VALUES
  ('task'), ('task_'), ('task_01'),
  ('taskA'), ('taskA01'), ('todo_01');

SELECT code
FROM tasks
WHERE code LIKE 'task_%'
ORDER BY code;

taskA
taskA01
task_
task_01

task_% requires at least one character after task. Thus task alone is excluded, while taskA and taskA01 match because A fills the _ position. The query is following its pattern; the pattern does not express the intended search.

How many characters do % and _ match?

% matches zero or more characters; _ matches exactly one. LIKE 'task%' includes task itself because % can match zero characters. LIKE 'task_' matches task_ and taskA, each with exactly one character after task.

The diagram compares two searches over the same codes. The first pattern accepts A in the underscore's position; the second requires an actual underscore:

Escaping the underscore makes the search select codes beginning with the literal task_ instead.

How do you search for a real underscore?

Choose an escape character; this example uses !. !_ means a literal _, while the final % still permits any number of following characters:

SELECT code
FROM tasks
WHERE code LIKE 'task!_%' ESCAPE '!'
ORDER BY code;

task_
task_01

Without ESCAPE '!', ! would be an ordinary character and _ would remain a wildcard. The SQLite LIKE documentation specifies a one-character escape value that makes a following _ or % literal. This example was checked with SQLite. String-literal and case-comparison rules may differ in another DBMS.

What should you check when results look wrong?

Decide whether you want a pattern or an exact character sequence. Use LIKE 'task%' for every code starting with task, code = 'task_' for exactly that one value, and LIKE 'task!_%' ESCAPE '!' for codes starting with a literal task_.

The same decision applies when application code builds a LIKE pattern from user input: should _ and % be search syntax or literal characters? Test both cases. If unexpected codes remain, check for a missing ESCAPE clause or an overly broad trailing %. A tiny table of values that must match and must not match is easier to reason about than a long query alone.

Key takeaways

In LIKE, % matches zero or more characters and _ matches exactly one. That is why LIKE 'task_%' includes taskA01. To require an underscore, use an explicit escape character, such as LIKE 'task!_%' ESCAPE '!', and compare the result against known included and excluded values.