Using SQLite

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 ...