Collation Functions
Collations are used to sort text values. They can be
used with ORDER BY or GROUP BY clauses, or for defining indexes.
You can also assign a collation to a table column, so that any index or
ordering operation applied to that column will automatically use a specific
collation. Above everything else, SQLite will always sort by datatype. NULLs
will always come first, followed by a mix of integer and floating-point
numeric values in their natural sort order. After the numbers come text
values, followed by BLOBs.
Most types have a clearly defined sort order. NULL types have no values, so they cannot be sorted. Numeric types use their natural numeric ordering, and BLOBs are always sorted using binary comparisons. Where things get interesting is when it comes to text values.
The default collation is known as the
BINARY collation. The BINARY collation sorts individual bytes
using a simple numeric comparison of the underlying character encoding. The
BINARY collation is also used for
BLOBs.
In addition to the default BINARY collation, SQLite includes a built-in
NOCASE and RTRIM collation that can be used with text values. The
NOCASE collation ignores character
case for the purposes of sorting 7-bit ASCII, and would consider the
expression 'A' == 'a' to be true. It does
not, however, consider 'Ä' == 'ä' to be
true, nor does it consider 'Ä' == 'A' to
be true, as the representations of these characters are outside of the ASCII
standard. The RTRIM collation (right-trim) is similar ...