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.
