How to Change the Collation of SQLite3 Database to Sort Case Insensitively

How to change the collation of sqlite3 database to sort case insensitively?

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;

How to set Sqlite3 to be case insensitive when string comparing?

You can use COLLATE NOCASE in your SELECT query:

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

Additionaly, in SQLite, you can indicate that a column should be case insensitive when you create the table by specifying collate nocase in the column definition (the other options are binary (the default) and rtrim; see here). You can specify collate nocase when you create an index as well. For example:

create table Test
(
  Text_Value  text collate nocase
);

insert into Test values ('A');
insert into Test values ('b');
insert into Test values ('C');

create index Test_Text_Value_Index
  on Test (Text_Value collate nocase);

Expressions involving Test.Text_Value should now be case insensitive. For example:

sqlite> select Text_Value from Test where Text_Value = 'B';
Text_Value      
----------------
b               

sqlite> select Text_Value from Test order by Text_Value;
Text_Value      
----------------
A               
b               
C    

sqlite> select Text_Value from Test order by Text_Value desc;
Text_Value      
----------------
C               
b               
A               

The optimiser can also potentially make use of the index for case-insensitive searching and matching on the column. You can check this using the explain SQL command, e.g.:

sqlite> explain select Text_Value from Test where Text_Value = 'b';
addr              opcode          p1          p2          p3                               
----------------  --------------  ----------  ----------  ---------------------------------
0                 Goto            0           16                                           
1                 Integer         0           0                                            
2                 OpenRead        1           3           keyinfo(1,NOCASE)                
3                 SetNumColumns   1           2                                            
4                 String8         0           0           b                                
5                 IsNull          -1          14                                           
6                 MakeRecord      1           0           a                                
7                 MemStore        0           0                                            
8                 MoveGe          1           14                                           
9                 MemLoad         0           0                                            
10                IdxGE           1           14          +                                
11                Column          1           0                                            
12                Callback        1           0                                            
13                Next            1           9                                            
14                Close           1           0                                            
15                Halt            0           0                                            
16                Transaction     0           0                                            
17                VerifyCookie    0           4                                            
18                Goto            0           1                                            
19                Noop            0           0                                            

SQLite - turn-off case sensitivity on whole database

You should set COLLATE NOCASE on the table. If the table already exist, use ALTER TABLE. See here sqlite.org for Description and Examples.

UPDATE MrGumble is right, SQLite does not support changing collation directly with ALTER TABLE. You would need to go through an intermediate table like this:

CREATE TABLE table01 (id, name countrycode, comment );
INSERT INTO table01 SELECT id, name, countrycode, comment FROM table;
DROP TABLE table;
CREATE TABLE table (
   id       integer PRIMARY KEY AUTOINCREMENT,
   name     text COLLATE NOCASE
   country  integer,
   comment  text COLLATE NOCASE
);
INSERT INTO table (id, name, countrycode, comment)
    SELECT id, name, countrycode, comment FROM table01;
DROP TABLE table01;

There is an Application called SQLite Maestro (with a 30 days free trial) that does this process for you.

How to use SQL Order By statement to sort results case insensitive?

You can also do ORDER BY TITLE COLLATE NOCASE.

Edit: If you need to specify ASC or DESC, add this after NOCASE like

ORDER BY TITLE COLLATE NOCASE ASC

or

ORDER BY TITLE COLLATE NOCASE DESC

SQLite/Room ORDER BY with a CASE statement ignores column COLLATE

The result of any CASE expression is an expression, even if its return value is a simple reference to a column like THEN stuff.

For this returned expression there is no explicitly defined Collating Sequence, so for the purposes of the ORDER BY clause comparisons the collating sequence that is used is BINARY.

The same would happen if the ORDER BY clause was just:

ORDER BY stuff || '' ASC

The above expression stuff || '' returns just the value of the column stuff, but still it is considered an expression and the BINARY collating sequence will be used.

If you want to apply a specific collating sequence to the result of the CASE expression you must use it explicitly in the ORDER BY clause:

SELECT * 
FROM test 
ORDER BY CASE WHEN true THEN stuff END COLLATE NOCASE ASC;

See the demo.

SQLite Query in non case sensitive alphabetical order

COLLATE goes before the order direction:

db.rawQuery("SELECT " + catName 
           + " FROM " +tableName 
        +" ORDER BY "+catName+" COLLATE NOCASE ASC;", null);

But you don't need the ASC -- that's the default so you could just as well use:

db.rawQuery("SELECT "+ catName 
            +" FROM "+ tableName 
        +" ORDER BY "+ catName +" COLLATE NOCASE;", null);

How to determine if SQLite column created with COLLATE NOCASE

How can I determine if the existing column is COLLATE NOCASE

The query

SELECT sql FROM sqlite_master WHERE type='table' AND tbl_name='my_table'

will give you the CREATE TABLE statement for that table. You could inspect the DDL to determine if the column is already defined as COLLATE NOCASE.

SQL alchemy case insensitive sort order

You need to reverse the ordering of your functions:

session.query(ResultsDBHistory).order_by(asc(func.lower(history_sort_order_column))).all()

so lower first, then declare the ascending order.

Alternatively, change the collation to NOCASE:

from sqlalchemy.sql import collate

session.query(ResultsDBHistory).order_by(asc(collate(history_sort_order_column, 'NOCASE'))).all()

which arguably is a better idea anyway.

I don't think the ASC is required, leaving that off simplifies your code somewhat:

from sqlalchemy.sql import collate

session.query(ResultsDBHistory).order_by(collate(history_sort_order_column, 'NOCASE')).all()

Case insensitive in SQLite

How about

SELECT id,name 
FROM trait 
WHERE UPPER(name) IN ("RATIO")

---

Related Topics