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%'