SQLite Forum

COLLATE - Multiple?

COLLATE - Multiple?

(1) By anonymous on 2021-01-20 08:43:31

How do you specify more than one COLLATE sequence in an SQL statement?

For instance, how do I specify NOCASE & RTRIM in the following?

SELECT * from myTable where name = 'ajay' COLLATE ?

(2) By David Raymond (dvdraymond) on 2021-01-20 14:04:36 in reply to 1 [link]

Basically you define your own collation that does both. I believe collations are basically just a function you give the two strings, and it returns whether A is less than B, B is less than A, or they're equal. They're not transformations applied to the strings before they're compared "normally".

But since rtrim is a function I believe an easier way to do this is

SELECT * from myTable where rtrim(name) = 'ajay' collate nocase;

(3) By Gunter Hick (gunter_hick) on 2021-01-21 07:48:23 in reply to 1 [link]

You can create your own custom collation function by calling the sqlite3_create_collation() family of functions right after connecting to the database file.

Your function can the do whatever it likes to process its arguments, as long as it implements a reflexive, transitive, antisymmetric, and total order.

see https://sqlite.org/c3ref/create_collation.html for details