SQLite LOWER and UPPER Change Only the Letters A to Z

Searching names with LOWER(name) = LOWER('Émile') works on PostgreSQL, MySQL and MariaDB. On SQLite, LOWER changes only A to Z, so É stays É and a stored émile never matches.

SQLite folds case for ASCII letters only, and so do its LIKE and COLLATE NOCASE, unless it is built with the ICU extension, as some systems ship it. Comparisons that use LOWER() to ignore case work on the other engines and fail on SQLite for names like Émile or Ødegaard.

What Each Engine Answered

The example

SELECT LOWER('ÉCOLE');

gave these answers:

The SQLite answers assume a build with ASCII-only case folding.

What to Write Instead

Fold case in the application before storing or comparing, or keep a lower-case copy of the column.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: SQLite's LOWER and UPPER change only the letters A to Z, so LOWER('ÉCOLE') is 'École' there and 'école' elsewhere. [lower-non-ascii]
    LOWER('ÉCOLE')
    Fold case in the application before storing or comparing, or keep a lower-case copy of the column.

1 problem: 1 warning.

baresql rules lower-non-ascii prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.