Case sensitive and insensitive like in SQLite

Question

In SQLite it is possible to change the case sensitive behaviour of 'LIKE' by using the commands:

PRAGMA case_sensitive_like=ON; PRAGMA case_sensitive_like=OFF; 

However in my situation I would like to execute a query, part of which is case sensitive and part of which isn't. For example:

SELECT * FROM mytable WHERE caseSensitiveField like 'test%' AND caseInsensitiveField like 'g2%' 

Is this possible?

Answer 1

You can use the UPPER keyword on your case insensitive field then upper-case your like statement. e.g.

SELECT * FROM mytable  WHERE caseSensitiveField like 'test%'  AND UPPER(caseInsensitiveField) like 'G2%' 

Answer 2

Use plain comparisons, which are case sensitive by default (unless you have declared the column COLLATE NOCASE):

SELECT * FROM mytable  WHERE caseSensitiveField >= 'test'   AND caseSensitiveField <  'tesu'   AND caseInsensitiveField LIKE 'g2%' 

This works only if the original LIKE is searching for a prefix, but allows using an index.