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

Question

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive?

Answer 1

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