SQLite Basics: Choose Text Comparison Rules with Collations

Text that looks similar to a person does not always compare equal in a database. A collation defines the comparison rule used for text equality and ordering. Choose that rule deliberately for names, codes, and padded imports instead of assuming every query is case-insensitive.

These examples use built-in collations and require SQLite 3.7.11 or later because their INSERT statements contain multiple VALUES rows. That is a syntax compatibility floor, not the version in which collations were introduced. Only the installed runtime below was executed.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

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

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

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()

1. Sort names without changing stored spelling

Save as sort-names.sql:

CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO contacts VALUES
    (1, 'alice'), (2, 'Bob'), (3, 'ALICE'), (4, 'carol');
SELECT 'binary', id, name FROM contacts
ORDER BY name COLLATE BINARY, id;
SELECT 'nocase', id, name FROM contacts
ORDER BY name COLLATE NOCASE, id;

Run:

python3 run-sql.py sort-names.sql

Output:

binary|3|ALICE
binary|2|Bob
binary|1|alice
binary|4|carol
nocase|1|alice
nocase|3|ALICE
nocase|2|Bob
nocase|4|carol

The first result places the uppercase spellings before lowercase alice. The second groups ALICE and alice together for sorting, with id resolving their tie. Every row still displays its original spelling: sorting has not rewritten the stored names.

Always include a tie-breaker when repeatable order matters. Two names that compare equal do not acquire a reliable internal order just because the current run happens to show them consistently. Here id makes the order total and visible, which is also helpful when testing a directory or export.

2. Give a code column a case-insensitive default

Save as lookup-codes.sql:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL COLLATE NOCASE
);
CREATE INDEX products_code ON products(code);
INSERT INTO products VALUES (1, 'ab-12'), (2, 'CD-34');
SELECT 'default', id, code FROM products WHERE code = 'AB-12';
SELECT 'exact matches', COUNT(*) FROM products
WHERE code COLLATE BINARY = 'AB-12';
SELECT 'ASCII', 'ab' = 'AB' COLLATE NOCASE;
SELECT 'non-ASCII', 'é' = 'É' COLLATE NOCASE;

Run:

python3 run-sql.py lookup-codes.sql

Output:

default|1|ab-12
exact matches|0
ASCII|1
non-ASCII|0

The column definition makes the ordinary equality lookup find ab-12. An explicit BINARY override makes that comparison case-sensitive. The index uses the column’s comparison rule too; a differently collated query should not be assumed to have the same index options.

The two final checks show an important limit: built-in NOCASE folds ASCII letters, not arbitrary Unicode text. Do not use it as a complete international-name matching policy. Decide whether your identifiers are restricted to ASCII, normalized before storage, or handled with an appropriate application-provided collation. This toy product code is ASCII, so the simpler rule is enough.

3. Compare padded codes without hiding the original input

Save as compare-padded-codes.sql:

CREATE TABLE imported_codes (id INTEGER PRIMARY KEY, code TEXT NOT NULL);
INSERT INTO imported_codes VALUES
    (1, 'P-8'), (2, 'P-8  '), (3, ' P-8'), (4, 'p-8');
SELECT 'binary', id, quote(code) FROM imported_codes
WHERE code = 'P-8' ORDER BY id;
SELECT 'rtrim', id, quote(code) FROM imported_codes
WHERE code COLLATE RTRIM = 'P-8' ORDER BY id;

Run:

python3 run-sql.py compare-padded-codes.sql

Output:

binary|1|'P-8'
rtrim|1|'P-8'
rtrim|2|'P-8  '

RTRIM lets the second imported code match despite its trailing spaces. quote() puts quotes around the output so those spaces remain noticeable. The leading-space row and lowercase row do not match. This makes the example useful for inspecting a fixed-width import without silently cleaning the source data.

RTRIM is a comparison rule, not an UPDATE or a general whitespace normalizer. It does not combine case folding with space handling, and a trailing tab is not the same as the ordinary trailing spaces used here. If the import needs permanent cleanup, define and review that separate transformation first.

The SQLite collation reference describes BINARY, NOCASE, RTRIM, and explicit overrides. The 3.7.11 release notes document multirow VALUES support. Test comparison rules with actual identifier examples before using them for uniqueness or account lookup.

Clean up

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

rm -- run-sql.py sort-names.sql lookup-codes.sql compare-padded-codes.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.