SQLite Basics: Match Text Deliberately with LIKE and GLOB

A search term containing an underscore or percent sign can match more rows than expected. LIKE treats those characters as patterns unless you escape them. GLOB uses a different pattern language and case rule, so switching between the two also changes what a search means.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. All three SQL files below were executed through the Python runner, and the output shown is observed output. The standalone SQLite CLI is not used.

Prepare a scratch directory

You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages or SQLite CLI are needed. Keep the same terminal open and save files as UTF-8. Stop if setup fails before saving any example files.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as run-sql.py. Each invocation starts a fresh in-memory database. Rows are pipe-separated, with missing values displayed as NULL. This teaching runner does not escape pipes or newlines inside values. Put each statement’s ending semicolon at the end of a line, and do not put two statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

All examples are independent. They use the built-in LIKE and GLOB implementations without extensions or changes to connection settings.

1. Distinguish a wildcard from a literal underscore

Save as match-report-prefix.sql:

CREATE TABLE files (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO files VALUES
    (1, 'report_2026.txt'),
    (2, 'reportX2026.txt'),
    (3, 'REPORT_2026.TXT'),
    (4, 'report.txt');
SELECT 'wildcard', id, name FROM files
WHERE name LIKE 'report_%.txt'
ORDER BY id;
SELECT 'literal underscore', id, name FROM files
WHERE name LIKE 'report!_%.txt' ESCAPE '!'
ORDER BY id;
SELECT 'case rules', 'a' LIKE 'A', 'æ' LIKE 'Æ';

Run:

python3 run-sql.py match-report-prefix.sql

Observed output:

wildcard|1|report_2026.txt
wildcard|2|reportX2026.txt
wildcard|3|REPORT_2026.TXT
literal underscore|1|report_2026.txt
literal underscore|3|REPORT_2026.TXT
case rules|1|0

The first pattern accepts X where the filename convention requires an underscore. The second makes that underscore literal while leaving the percent wildcard active. LIKE’s percent sign matches zero or more characters; its underscore matches one character. ESCAPE names a single character used to quote pattern metacharacters. LIKE expressions.

The uppercase filename still matches. Built-in LIKE folds ASCII case, but not general Unicode case, as the final comparison shows. Do not promise language-aware case-insensitive search based on this behavior. NULL input also needs a separate policy: a LIKE comparison against NULL does not become a true match.

2. Escape all special characters in a literal search term

Save as search-literal-discount.sql:

CREATE TABLE offers (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
INSERT INTO offers VALUES
    (1, 'Deal 50%_off! now'),
    (2, 'Deal 50XXoff! now'),
    (3, '50%_off!'),
    (4, '50%_off?');
WITH search(term) AS (VALUES ('50%_off!')),
patterns AS (
    SELECT '%' ||
           replace(replace(replace(term, '!', '!!'), '%', '!%'), '_', '!_') ||
           '%' AS value
    FROM search
)
SELECT o.id, o.label
FROM offers AS o CROSS JOIN patterns AS p
WHERE o.label LIKE p.value ESCAPE '!'
ORDER BY o.id;

Run:

python3 run-sql.py search-literal-discount.sql

Observed output:

1|Deal 50%_off! now
3|50%_off!

The search term includes all three troublesome characters: percent, underscore, and the chosen escape character. Escape ! first, then % and _. Reversing that order could double the escape markers that earlier replacements just inserted. The two outer percent signs are deliberately left active to request a substring search.

This is pattern escaping, not SQL-string escaping. In application code, keep SQL fixed and bind the completed pattern as a parameter. Binding a raw string containing % prevents it from becoming SQL syntax, but it does not make the percent sign literal to LIKE. The two protections address different problems. Python SQLite placeholders.

These are non-empty toy search terms. Decide whether an empty search should match everything before exposing a contains-search control. Leading-wildcard searches can also be expensive on large tables; this example demonstrates matching, not performance.

3. Use GLOB for case-sensitive filename shapes

Save as glob-log-filenames.sql:

CREATE TABLE files (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO files VALUES
    (1, 'log-1.txt'),
    (2, 'log-12.txt'),
    (3, 'LOG-2.txt'),
    (4, 'log-3.csv'),
    (5, 'log-?.txt'),
    (6, 'log-4.txt');
SELECT 'one digit', name FROM files
WHERE name GLOB 'log-[0-9].txt'
ORDER BY name;
SELECT 'literal question mark', name FROM files
WHERE name GLOB 'log-[?].txt'
ORDER BY name;

Run:

python3 run-sql.py glob-log-filenames.sql

Observed output:

one digit|log-1.txt
one digit|log-4.txt
literal question mark|log-?.txt

The bracket range consumes exactly one digit. It excludes the two-digit name and the uppercase LOG prefix. Putting the question mark in a bracket set makes it a literal character in this pattern.

GLOB is case-sensitive and uses Unix-style glob patterns: * matches a sequence and ? matches one character. It has no LIKE-style ESCAPE clause. Neither syntax is a regular expression. In particular, [0-9]* means a digit followed by any sequence, not a sequence containing only digits. SQLite GLOB patterns.

Choose equality for an exact identifier, an escaped LIKE pattern for a literal substring search, or GLOB for a deliberate case-sensitive shape. Keep test cases containing the pattern characters themselves; ordinary alphabetic examples do not expose escaping mistakes.

Clean up

In the same terminal, remove only the named example files and the empty scratch directory:

rm -- run-sql.py match-report-prefix.sql search-literal-discount.sql glob-log-filenames.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.