Sqlite - Collation (String comparison)

Sqlite Banner

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





Discover More
Card Puncher Data Processing
Datacadamia - Data all the things

Computer science from a data perspective
Sqlite Banner
SQLite - Column

beware !!: why_alter_table_is_such_a_problem_for_sqlite Since SQLite 3.25.0. Alter...



Share this page:
Follow us:
Task Runner