[ACCEPTED]-How to change the collation of sqlite3 database to sort case insensitively?-case-insensitive

Accepted answer
Score: 24

To sort it Case insensitive you can use 1 ORDER BY Name COLLATE NOCASE

Score: 13

The SQLite Datatypes documentation discusses user-defined collation 6 sequences. Specifically you use COLLATE 5 NOCASE to achieve your goal.

They give an 4 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 3 using the NOCASE collation sequence (i.e. values -- 'abc' and 2 'ABC' are placed in the same group). SELECT 1 count(*) GROUP BY d FROM t1;

Score: 1
select * from tableNames Order by lower(Name);

Michael van der Westhuizen explains in his 5 comment below why this is not a good way. I 4 am leaving this answer up so as to preserve 3 his comment and to serve as a warning to 2 others who might have the same 'bright' idea 1 I had ;-)

Score: 0

Use this statement in your SQLite database:

PRAGMA case_sensitive_like = false

0

More Related questions