Case sensitive and insensitive like in SQLite Case sensitive and insensitive like in SQLite sqlite sqlite

Case sensitive and insensitive like in SQLite


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


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.


I know this is an old question, but if you are coding in Java and have this problem this might be helpful. You can register a function that handles the like checking. I got the tip form this post: https://stackoverflow.com/a/29831950/1271573

The solution i dependent on sqlite jdbc: https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

In my case I only needed to see if a certain string existed as part of another string (like '%mystring%'), so I created a Contains function, but it should be possible to extend this to do a more sql-like check using regex or something.

To use the function in SQL to see if MyCol contains "searchstring" you would do:

select * from mytable where Contains(MyCol, 'searchstring')

Here is my Contains function:

public class Contains extends Function {    @Override    protected void xFunc() throws SQLException {        if (args() != 2) {            throw new SQLException("Contains(t1,t2): Invalid argument count. Requires 2, but found " + args());        }        String testValue = value_text(0).toLowerCase();        String isLike = value_text(1).toLowerCase();        if (testValue.contains(isLike)) {            result(1);        } else {            result(0);        }    }}

To use this function you must first register it. When you are done with using it you can optionally destroy it. Here is how:

public static void registerContainsFunc(Connection con) throws SQLException   {    Function.create(con, Contains.class.getSimpleName(), new Contains());}public static void destroyContainsFunc(Connection con) throws SQLException {    Function.destroy(con, Contains.class.getSimpleName());}