Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content