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?
Sqlite – How to set Sqlite3 to be case insensitive when string comparing
case-insensitivesqlite
Related Topic
- C# – Case insensitive ‘Contains(string)’
- Sqlite – Improve INSERT-per-second performance of SQLite
- How to do case insensitive search in Vim
- Android – How to avoid concurrency problems when using SQLite on Android
- C# – How to do a case insensitive string comparison
- C# – Case-Insensitive List Search
- Java – Case insensitive string as HashMap key
- Javascript – Contains case insensitive
Best Answer
You can use
COLLATE NOCASE
in yourSELECT
query: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 arebinary
(the default) andrtrim
; see here). You can specifycollate nocase
when you create an index as well. For example:Expressions involving
Test.Text_Value
should now be case insensitive. For example: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.: