SQL Wildcards: Match a Prefix and a Literal Underscore

The practice task

Find fictional course codes beginning with the literal prefix course_. Keep courseX1 in the fixture to show why an unescaped underscore would select a different population.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE codes(code TEXT);
INSERT INTO codes VALUES ('course_1'),('course_21'),('courseX1'),('topic_1');

SELECT code FROM codes
WHERE code LIKE 'course\_%' ESCAPE '\'
ORDER BY code;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The backslash escape makes the underscore literal. Percent then permits any remaining suffix, so both course_1 and course_21 qualify. courseX1 is excluded because it has X where the literal underscore is required.

Without the escape, the underscore could match a single character such as X. That would change the code family being selected. A pattern that looks visually close to a prefix can therefore include records outside the intended naming rule.

Matching behavior also depends on the database and its collation or Unicode support. This SQLite exercise uses ASCII codes. For user-facing names or languages with non-ASCII case rules, test the actual engine configuration instead of assuming every LIKE implementation has identical case behavior.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Use an explicit ESCAPE character when matching a literal wildcard.
• Test an intentionally similar non-matching code.
• Do not confuse pattern matching with a full regular-expression language.