This is the 16th day of my participation in the August More Text Challenge

SQLite is a C language library that implements a small, fast, self-contained, highly reliable, full-featured SQL database engine. Built on all phones and most computers, or bundled into applications.

As a self-contained single file database, it can use THE SYNTAX of SQL and the theory of database system to manage and store data conveniently and efficiently, and can be portable with the application of any other location and platform, especially in the mobile APP development is very widely used. The latest version of SQLite is 3.36.0.

Introduction to Sorting Rules

As an introduction to character sets and Collation, SQLite and PostgreSQL are the only case-sensitive default Collation databases that exist.

SQLite compares strings using a collating sequence or collating function. The built-in functions are BINARY, NOCASE, and RTRIM.

  • BINARY: a BINARY comparison is used.
  • RTRIM: Same as binary, except that trailing whitespace is ignored.
  • NOCASE: case insensitive comparison character.

Additional collation functions can be registered using sqlite3_create_collation().

The collation function is only useful for comparing string values. Numeric values always compare numeric values; A BLOB is always a byte by byte comparison.

Collation of columns in SQLite

Each column of each table has a collation function. If not explicitly defined, the default is BINARY.

That’s why SQLIte is case sensitive by default.

You can specify collation rules for comparison and sorting through the COLLATE clause.

The COLLATE clause can also be defined on a column when a table is created.

Actually testing SQLite’s collation rules

As follows, go to SQLite and create the OrderTest table to insert the data.

CREATE TABLE OrderTest(
  letter char(1) NOT NULL
);
INSERT INTO OrderTest values('B'), ('b'), ('A'), ('a');
Copy the code
  • To view the default comparison results:
sqlite> select * from OrderTest where letter='a';
a
sqlite> select * from OrderTest where letter='A';
A
Copy the code
  • SQLite performs comparisons case-insensitive:
sqlite> select * from OrderTest where letter collate nocase ='A';
A
a
Copy the code
  • Order by clause in SQLite, default result:
sqlite> select letter from OrderTest order by letter;
A
B
a
b
Copy the code
  • Order by clause in SQLite specifies a case-insensitive collation NOCASE:
sqlite> select letter from OrderTest order by letter collate nocase;
A
a
B
b
Copy the code

Use the upper() or lower() functions

The upper() or lower() functions are also supported in SQLIte.

String comparison, sorting, and Group by are case insensitive and can be used in addition to specifying collation rules.