Case sensitive and insensitive like in SQLite

In SQLite you can use GLOB instead of LIKE for pattern search. For example:

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

With this approach you don't have to worry about PRAGMA.

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.

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

Related