Why does a BINARY code column still match lowercase prefixes with LIKE?

Prepare the report input

This original rule treats Ab and ab as different prefixes. SQLite LIKE normally folds ASCII case even though the column uses BINARY comparisons. For the fixed literal Ab prefix here, GLOB Ab* enforces case-sensitive matching.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE codes(code TEXT COLLATE BINARY PRIMARY KEY); INSERT INTO codes VALUES('Ab1'),('ab2'),('Ax3');

Compare misleading and corrected results

SELECT code FROM codes WHERE code LIKE 'Ab%' ORDER BY code;

SELECT code FROM codes WHERE code GLOB 'Ab*' ORDER BY code;

LIKE retains Ab1 and ab2; GLOB retains Ab1. GLOB has its own wildcard syntax, so arbitrary prefixes need their own literal-escaping or substring policy.

Check a fresh boundary case

Adding uppercase AB4 does not add a match for the case-sensitive Ab prefix.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO codes VALUES('AB4');
SELECT code FROM codes WHERE code GLOB 'Ab*' ORDER BY code;