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.
|RTRIM||Ignores trailing whitespace|
|NOCASE||Case-insensitive for ASCII characters A-Z|
|BINARY||Case-sensitive. Compares bytes directly|
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 count = (long)queryCommand.ExecuteScalar();
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.