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 ...
Get Using SQLite now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.