When querying for a string in SQLite it performs a case sensitive comparison by default. To make case insensitive string comparisons in SQLite, COLLATE NOCASE keyword can be used next to the column being compared as below,
SELECT * FROM tblname WHERE txt_col1 = 'test_val' COLLATE NOCASE and txt_col2 = 'test_val2';
-- case insensitive sorting
SELECT * FROM tblname ORDER BY txt_col1 COLLATE NOCASE DESC;
COLLATE NOCASE keyword only applies to column or expression it appears next to and does not apply to other text columns being compared. In the above example, case insensitive matching will only be performed against txt_col1 and case sensitive matching will be performed against txt_col2.
When COLLATE NOCASE is specified, SQLite folds the string to lower case and performs comparison. It can also be specified during creation of table as show below, so that it does not have specified during every query.
CREATE TABLE tblname1 ( txt_col1 TEXT COLLATE NOCASE, txt_col2 TEXT);
If specified during table creation on a column with unqiue index then it does not allow same string with different cases to be inserted and returns unique constraint violation for such data.
Note:
SQLite only supports COLLATE NOCASE for ASCII chars, and does not do full UTF case folding and will not work as expected for non ASCII UTF chars.
Using COLLATE NOCASE during table creation provides performance benefits of using index if the column under comparison has an index.
Other SQL standard approaches to perform case insensitive comparisons are, using UPPER() or LOWER() functions to convert both columns to same case.
References: