Convenient way to use LIKE case sensivity in sqlite

Question

As I see LIKE operator can optimize query if I switch PRAGMA case_sensitive_like=ON. I measured, it really worked, queries "LIKE someth%" becomes ten times faster on a compartively large binary indexed tables. But the problem is that my library implemented as an add-on to my application, it maintains its own tables with any db it is connected. So the problems are

• I can not read case_sensitive_like since it is only supported to be set, not read. So I can not temporarily read the state and return it after the query,
• As an addon that should obey the main functionality of the db, I should not change the setting to my need for good since it can affect other routines.
• As I see there's no Like(case-sensitive) internal equivalent for me to call optimizid query directly. For example use LIKECASESENSITIVE instead of LIKE
• I can call sqlite3_create_function, but I don't know whether or not I can call LIKE(CASE SENSITIVE) internally.

Answer 1

If you can change the schema, why not add a new column to the table that always contains the lower-case version of the value you want to find? Then you can rewrite your query like:

SELECT ... FROM table WHERE col_lower_case LIKE 'search string%';

This also has the advantage, that an index on col_lower_case can always be used to speed up prefix-searches.

Answer 2

I can not read case_sensitive_like since it is only supported to be set, not read. So I can not temporarily read the state and return it after the query

You can get the state of case_sensitive_like with a query like this one:

select case when 'a' like 'A' then 0 else 1 end

which will return 1 if case_sensitive_like = ON and 0 if it's OFF.