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());}