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