Hope the following helps you

➜  data sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t (a TEXT);
sqlite> INSERT INTO t VALUES ('foo'), ('Foo'), ('fOO');
sqlite> SELECT * FROM t;
a
---
foo
Foo
fOO
sqlite> SELECT * FROM t WHERE a LIKE 'f%';
a
---
foo
Foo
fOO
sqlite> PRAGMA case_sensitive_like = true;
sqlite> SELECT * FROM t WHERE a LIKE 'f%';
a
---
foo
fOO
sqlite> PRAGMA case_sensitive_like = false;
sqlite> SELECT * FROM t WHERE a LIKE 'f%';
a
---
foo
Foo
fOO
sqlite>

Thank you for taking the time to respond to my question. I understood this before posting my question and likely did not word the question clearly.

Take IN, for example. In your sample, one could:

sqlite> select * from t where a in ('foo');
foo

Or one could:

sqlite> select * from t where a collate nocase in ('foo');
foo
Foo
fOO

and without changing a PRAGMA.

Regarding the use of LIKE because IN doesn't work with %, I wanted to know:

1. Is it possible to alter the case-sensitivity without changing the PRAGMA? Is there an equivalent of collate nocase for LIKE, if the PRAGAM is set to case sensitve?

2. And, if not, is it "okay" to modify PRAGMA multiple times in a program before and after a query?

I'm only a novice among novices and got the impression that PRAGMAs are something to be set for how one wants their SQLite copy to function and not something that should be programmatically altered often within an application. That impression may be very inaccurate; I don't know.

Thanks again.

Could you perhaps keep the LIKE command case-insensitive, then rephrase only those comparisons that need to be case-sensitive using GLOB instead of LIKE (since GLOB will remain case-sensitive)?

Note that GLOB uses '?' and '*' special characters and not '_' or '%'.

If you link in a REGEXP function then of course you have even more cababilities.

On the other hand, Simon's suggestion of keeping two versions of the strings would allow you to perform further transformations than simply LOWER() if needed to get your strings to your own definition of "canonical form" for comparisons.

Thank you for the suggestion. I'll give it a try. This sounds like a good choice for my case because I'd expect most searches to be case insenstive, or case sensitive on a complete word. I just wanted it to work if a searcher wanted a case-sensitve match on a word fragment, most likely a proper name with and without 's; like Bob% to return Bob, Bob's, Bobby but not bobber, bobsled, etcetera.

As you wrote, Simon's suggestion would offer the most flexibility and wouldn't miss an index with something like a WHERE lower(column_name) like lower('Some Text')%.

https://www.sqlite.org/pragma.html#pragma_case_sensitive_like

This pragma is deprecated and exists for backwards compatibility only. New applications should avoid using this pragma. Older applications should discontinue use of this pragma at the earliest opportunity. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED.

I was surprised that

pragma case_sensitive_like

does not return the status, as many other pragmas do.

It does seem a little inconsistent, but presumably you would only ever want to set it idempotently so I can't see why that matters.

Case insensitivity is language dependent. Only in English it's as simple as it appears to be.

Is it accurate that the case-sensitivity options in SQLite applites only to the ASCII and all else is always case sensitive?

If one is dealing with multi-byte characters and occasionally wants a case-insentive match can SQLite handle it?

Is it accurate that the case-sensitivity options in SQLite applites only to the ASCII and all else is always case sensitive?

Correct. Natively only single-byte characters are folded. Multibyte characters are unmolested.

If one is dealing with multi-byte characters and occasionally wants a case-insentive match can SQLite handle it?

That depends what you mean by "can SQLite3 handle it".

If you mean the as-distributed library or debugging CLI, the answer is no.

If you mean can you write code to do that and add it to sqlite3, then only you can answer this because only you know if you are capable of writing an extension to SQLite3 that does this.

If you mean can a third-party write code that does this that can be incorporated into SQLite3 (eg, ICU and others) -- the answer is yes.

In situations where you need case-insensitive searching, and it's difficult to do that inside your search command, store two versions of the string: one as it was when presented, and another which is converted to lower case (or upper case, if you prefer). Then when you need to search in a case-insensitive way, just search the converted one.