Edit

Collation

  • Article

Collating sequences are used by SQLite when comparing TEXT values to determine order and equality. You can specify which collation to use when creating columns or per-operation in SQL queries. SQLite includes three collating sequences by default.

Collation Description
RTRIM Ignores trailing whitespace
NOCASE Case-insensitive for ASCII characters A-Z
BINARY Case-sensitive. Compares bytes directly

Custom collation

You can also define your own collating sequences or override the built-in ones using CreateCollation. The following example shows overriding the NOCASE collation to support Unicode characters. The full sample code is available on GitHub.

connection.CreateCollation("NOCASE", (x, y) => string.Compare(x, y, ignoreCase: true));

var queryCommand = connection.CreateCommand();
queryCommand.CommandText =
@"
    SELECT count()
    FROM greek_letter
    WHERE value = 'λ' COLLATE NOCASE
";
var oCount = queryCommand.ExecuteScalar();
var count = (oCount is not null) ? (int)oCount : -1;

Like operator

The LIKE operator in SQLite doesn't honor collations. The default implementation has the same semantics as the NOCASE collation. It's only case-insensitive for the ASCII characters A through Z.

You can easily make the LIKE operator case-sensitive by using the following pragma statement:

PRAGMA case_sensitive_like = 1

See User-defined functions for details on overriding the implementation of the LIKE operator.

See also