Sqlite - Collation (String comparison)
Table of Contents
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
- and collate operator