Cover by Jay A. Kreibich

Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo

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

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required