SQLite sorts text using a collation. By default this is case sensitive, which often leads to surprising orderings when data contains mixed casing. SQLite provides a built-in solution for simple use cases: COLLATE NOCASE.

Basic usage

You can apply COLLATE NOCASE directly in an ORDER BY clause:

SELECT name
FROM users
ORDER BY name COLLATE NOCASE;

This sorts Alice, bob, and charlie as you would typically expect, regardless of their original casing.

You can also use it in comparisons:

SELECT *
FROM users
WHERE name = 'pieter' COLLATE NOCASE;

Defining it at column level

If a column is almost always queried case-insensitively, you can define the collation in the schema:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT COLLATE NOCASE
);

All comparisons and sorting on name will now default to case-insensitive behavior.

Indexes and performance

Indexes are collation-aware in SQLite. If you sort case-insensitively, make sure your index uses the same collation:

CREATE INDEX users_name_nocase_idx
ON users(name COLLATE NOCASE);

Without this, SQLite may ignore the index for ORDER BY name COLLATE NOCASE.

Limitations to be aware of

COLLATE NOCASE is ASCII-only. It handles A–Z and a–z, but does not perform full Unicode case folding. Characters like é or ß are not handled correctly.

If you need proper internationalized sorting, you have a few options:

• Normalize and store a secondary, lowercased column
• Use ORDER BY LOWER(name) (simple but index-unfriendly)
• Compile SQLite with the ICU extension and use ICU collations

When to use it

COLLATE NOCASE is a pragmatic choice when:

• Your data is primarily ASCII
• You want predictable, simple case-insensitive sorting
• You care about index-backed performance

For many applications, it is the right balance between correctness and simplicity.