How to change the collation of sqlite3 database to sort case insensitively?
To sort it Case insensitive you can use ORDER BY Name COLLATE NOCASE
The SQLite Datatypes documentation discusses user-defined collation sequences. Specifically you use COLLATE NOCASE to achieve your goal.
They give an example:
CREATE TABLE t1( a, -- default collation type BINARY b COLLATE BINARY, -- default collation type BINARY c COLLATE REVERSE, -- default collation type REVERSE d COLLATE NOCASE -- default collation type NOCASE);and note that:
-- Grouping is performed using the NOCASE collation sequence (i.e. values-- 'abc' and 'ABC' are placed in the same group).SELECT count(*) GROUP BY d FROM t1;
select * from tableNames Order by lower(Name);Michael van der Westhuizen explains in his comment below why this is not a good way. I am leaving this answer up so as to preserve his comment and to serve as a warning to others who might have the same 'bright' idea I had ;-)