Sqlite - Collation (String comparison)

About

Collation in Sqlite

Example

Select Insensitive Equality

select
    name
from
    persons
where
    name = 'foo' COLLATE NOCASE;

This query is case insensitive and will match:

  • FOO
  • fOo

Select Insensitive Pattern Comparison

select
    name
from
    persons
where
    like '%foo%' COLLATE NOCASE;

This query is case insensitive and will match:

  • FOO bar
  • fOo Bar

The pattern case insensitive seems not to work with the glob operator

Table

Example of default collation defined on the column itself

create table persons
(
  name text collate nocase
);

Syntax

Collate can be assigned by default on the table and overwritten on the query.

For the syntax, see


Powered by ComboStrap