Edit

Share via


Collation

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