How to set Sqlite3 to be case insensitive when string comparing?
case-insensitive, sqlite
Solution
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
Problem
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?