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

by

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

More Related Contents:

  1. near “varchar”: syntax error [closed]
  2. How do I make an UPDATE while joining tables on SQLite?
  3. How do I rename a column in a SQLite database table?
  4. Accessing an SQLite Database in Swift
  5. Import CSV to SQLite
  6. Difference between 2 dates in SQLite
  7. Update with Join in SQLite
  8. Can I read and write to a SQLite database concurrently from multiple connections?
  9. How do I store and retrieve a blob from sqlite?
  10. Sqlite primary key on multiple columns
  11. Store boolean value in SQLite
  12. Can SQLite support multiple users?
  13. SQLite add Primary Key
  14. How delete table inner join with other table in Sqlite?
  15. Export from sqlite to csv using shell script
  16. SQLite: How do I save the result of a query as a CSV file?
  17. Create table in SQLite only if it doesn’t exist already
  18. How do DATETIME values work in SQLite?
  19. Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created
  20. Is there a boolean literal in SQLite?
  21. sqlite get ROWID
  22. svn cleanup: sqlite: database disk image is malformed
  23. Will SQLite performance degrade if the database size is greater than 2 gigabytes?
  24. Truncate a SQLite table if it exists?
  25. SQLite for large data sets?
  26. SQLite database supporting Unicode data
  27. Integration Testing Entity Framework code first with in-memory database
  28. How to get the number of rows of the selected result from sqlite3?
  29. How to get the last index of a substring in SQLite?
  30. SQLITE3 VACUUM, “database or disk is full”

Leave a Comment