If you’ve spent any time writing search features with SQL, you’ve probably used the LIKE operator to find text that partially matches a pattern. What surprises a lot of people, though, is that SQLite’s LIKE operator is case-insensitive by default — at least for standard ASCII characters. That means WHERE name LIKE 'john' will happily match “John,” “JOHN,” and “jOhN” all at once.
Sometimes that’s exactly the behavior you want. Other times, it’s not — maybe you’re building a case-sensitive search feature, or your data relies on case to distinguish between meaningfully different values. That’s where the case_sensitive_like PRAGMA comes in. Let’s dig into exactly how it works, when to use it, and the quirks you need to know about.
What Is the case_sensitive_like PRAGMA?
The case_sensitive_like PRAGMA controls whether the LIKE operator treats uppercase and lowercase letters as equivalent when comparing strings. By default, this PRAGMA is off, meaning LIKE comparisons are case-insensitive. When you turn it on, LIKE becomes case-sensitive, and comparisons will only match when the case of the letters lines up exactly.
This is a connection-level setting, meaning it applies only to the current database connection and needs to be re-applied every time you open a new connection if you want the behavior to persist.
Basic Syntax
Turning case-sensitive matching on:
PRAGMA case_sensitive_like = ON;
Turning it back off (restoring the default, case-insensitive behavior):
PRAGMA case_sensitive_like = OFF;
Unlike some other PRAGMAs, this one doesn’t have a simple query form that returns its current state — case_sensitive_like is what’s called a “settable-only” PRAGMA in SQLite. You can set it, but you can’t directly ask SQLite what its current value is with a plain PRAGMA case_sensitive_like; query. If you need to track its state in your application, you’ll need to manage that yourself, since SQLite won’t report it back to you.
How LIKE Behaves By Default
To really appreciate what this PRAGMA does, it helps to see the default behavior first.
CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT);
INSERT INTO users (username) VALUES ('Alice'), ('alice'), ('ALICE'), ('Bob');
SELECT * FROM users WHERE username LIKE 'alice';
By default, this query returns all three “alice” variants — “Alice,” “alice,” and “ALICE” — because LIKE ignores case for ASCII letters out of the box.
Now let’s see what happens once we enable case-sensitive matching:
PRAGMA case_sensitive_like = ON;
SELECT * FROM users WHERE username LIKE 'alice';
With the PRAGMA turned on, this query now returns only the row where username is exactly “alice” — lowercase, matching precisely. “Alice” and “ALICE” are excluded because their capitalization doesn’t match the pattern.
Practical Examples
Let’s walk through a handful of scenarios that show how this plays out in real use.
Example 1: Wildcard matching with case sensitivity
PRAGMA case_sensitive_like = ON;
SELECT * FROM users WHERE username LIKE 'A%';
With case sensitivity turned on, this only matches usernames that start with an uppercase “A” — so “Alice” matches, but “alice” does not.
Example 2: Case-insensitive search (the default)
PRAGMA case_sensitive_like = OFF; -- explicit, though this is the default
SELECT * FROM products WHERE name LIKE '%widget%';
This will match “Widget,” “WIDGET,” “super-Widget-2000,” and so on, regardless of capitalization — ideal for user-facing search boxes where people don’t want to worry about exact casing.
Example 3: Combining with escape characters
SELECT * FROM files WHERE filename LIKE '%\_backup%' ESCAPE '\';
Case sensitivity settings apply independently of escape character handling — you can combine case_sensitive_like with ESCAPE clauses to search for literal underscores or percent signs while still controlling case behavior.
Example 4: Case sensitivity only affects ASCII characters
This is a detail that trips a lot of people up:
PRAGMA case_sensitive_like = OFF;
SELECT * FROM cities WHERE name LIKE 'MÜNCHEN';
Even with case-insensitive matching enabled, SQLite’s built-in LIKE only performs case folding for ASCII letters (A-Z and a-z) unless you’ve loaded an extension like ICU (International Components for Unicode). That means “münchen” won’t automatically match “MÜNCHEN” through case-insensitive comparison, because the ü/Ü pair falls outside SQLite’s default case-folding logic. If you need proper case-insensitive matching for accented or non-Latin characters, you’ll need the ICU extension, which provides Unicode-aware LIKE and UPPER/LOWER behavior.
Common Use Cases
Here’s where developers typically reach for this PRAGMA:
- Case-sensitive identifier searches. Systems where case genuinely matters — like matching exact product SKUs, API keys, or hash-like identifiers — benefit from turning this on to avoid false-positive matches.
- Security-sensitive lookups. If usernames or tokens are case-sensitive by design (which is common in systems that allow both “Admin” and “admin” as separate accounts), enabling case-sensitive
LIKEprevents accidental cross-matching. - Data validation and auditing. When checking for exact-case formatting compliance (say, verifying that country codes are stored in uppercase), a case-sensitive
LIKEsearch can help identify inconsistent entries. - Preserving default behavior for general search. Most user-facing search features actually want the default, case-insensitive behavior, so many developers explicitly document (or explicitly set)
OFFjust to make the intended behavior clear in their setup scripts, even though it’s the default.
Important Considerations
This PRAGMA affects the entire connection. Once set, it applies to every LIKE comparison run through that connection until you change it again or close the connection. If different parts of your application need different behavior, you’ll need to toggle the PRAGMA before running the relevant queries, or better yet, use a different mechanism for case-sensitive comparisons where needed (see below).
GLOB is always case-sensitive, regardless of this PRAGMA. SQLite has another pattern-matching operator, GLOB, which uses Unix shell-style wildcards (* and ? instead of % and _). Unlike LIKE, GLOB is always case-sensitive and is completely unaffected by the case_sensitive_like PRAGMA. If you need guaranteed case-sensitive pattern matching without worrying about connection-level settings, GLOB can be a more predictable choice.
SELECT * FROM users WHERE username GLOB 'Alice';
This always matches only the exact-case string, no PRAGMA required.
Indexes and performance. Changing case sensitivity doesn’t inherently make LIKE queries faster or slower on its own, but it interacts with how SQLite can use indexes. A LIKE query with a pattern that doesn’t start with a wildcard (like 'abc%') can potentially use an index for a prefix scan, but only if the column’s collation matches the comparison mode being used. Mismatches between your collation settings and case sensitivity mode can prevent SQLite from using an index efficiently, forcing a full table scan instead.
Case sensitivity is separate from collation. It’s easy to conflate this PRAGMA with column collations like NOCASE, but they’re different mechanisms. A column defined with COLLATE NOCASE will behave in a case-insensitive way for equality comparisons (=) and sorting (ORDER BY), independent of the case_sensitive_like PRAGMA, which only affects the LIKE operator specifically.
Best Practices
- Leave it at the default unless you have a specific reason to change it. Most search functionality benefits from case-insensitive matching, and that’s what users expect from a search box.
- Be explicit in your codebase. If your application relies on a particular case-sensitivity behavior, set the PRAGMA explicitly at the start of every connection rather than assuming the default — this protects you if SQLite’s defaults ever change, and makes your intent clear to other developers.
- Consider GLOB for guaranteed case-sensitive matching. If case sensitivity is a hard requirement rather than a toggle,
GLOBavoids any ambiguity tied to connection state. - Use COLLATE NOCASE at the column level for consistent case-insensitive equality. If you frequently need case-insensitive comparisons beyond just
LIKE— for example, inWHERE username = ?clauses — defining the column withCOLLATE NOCASEgives you consistent behavior across operators, not justLIKE. - Reach for the ICU extension if you need true Unicode case folding. The built-in case-insensitive behavior only covers ASCII; if your application handles international text and needs accurate case-insensitive matching for accented characters, load the ICU extension.
- Document any non-default settings. Because this PRAGMA has no simple way to query its current state, leaving a comment near where you set it (or centralizing your PRAGMA configuration in one place, like a connection initialization function) will save confusion down the line.
Troubleshooting Common Issues
I set case_sensitive_like = ON but my search still seems case-insensitive. The most likely explanation is that the PRAGMA was set on a different connection than the one running your query. Because this is a per-connection setting, if your application uses a connection pool or opens a fresh connection for each request, the PRAGMA needs to be reapplied every single time a new connection is established. Check your connection initialization logic to confirm the PRAGMA is actually being executed on the connection handling your query.
My case-insensitive search isn’t matching accented characters correctly. As covered above, this is expected behavior — SQLite’s default LIKE case-folding only covers plain ASCII letters. If your application needs to handle accented or non-Latin characters case-insensitively, you’ll need to load the ICU extension, which isn’t compiled into every SQLite distribution by default, so you may need to check whether your particular build or binding supports loading extensions at all.
I’m not sure whether case_sensitive_like is currently on or off. Since this PRAGMA doesn’t support the query form, you can’t ask SQLite directly. The most reliable approach is to centralize your PRAGMA configuration in one place in your codebase (a connection setup function, for example) so you always know exactly what state you’ve left it in, rather than trying to infer it later.
Case sensitivity seems inconsistent between different queries in the same session. If different parts of your codebase toggle this PRAGMA for different purposes without resetting it afterward, you can end up with confusing, seemingly inconsistent behavior. It’s worth auditing your codebase for every place this PRAGMA gets set, and considering whether a more predictable approach — like using GLOB for guaranteed case-sensitive matching regardless of connection state — would simplify things.
Frequently Asked Questions
Does case_sensitive_like affect the = operator or ORDER BY?
No. This PRAGMA only affects the LIKE operator specifically. Standard equality comparisons (=) and sorting (ORDER BY) are governed by the column’s collation sequence, which is a completely separate mechanism (commonly BINARY by default, or NOCASE if explicitly defined on the column).
Is there a way to make LIKE case-insensitive for a single query without changing the connection-wide setting?
Not directly through the PRAGMA itself, since it applies at the connection level. However, you can work around this by using LOWER() or UPPER() on both sides of the comparison to force a specific case behavior regardless of the PRAGMA’s current state: WHERE LOWER(username) LIKE LOWER('alice%'). This approach is explicit and doesn’t depend on connection configuration at all.
Does turning this PRAGMA on affect index usage?
It can, indirectly. If a column has a COLLATE NOCASE definition and an index built on that collation, changing case_sensitive_like doesn’t change the column’s collation itself, but mismatches between how you’re comparing text and how it’s indexed can prevent SQLite from using that index efficiently. It’s worth checking your query plans with EXPLAIN QUERY PLAN if you’re relying heavily on LIKE performance.
Why doesn’t SQLite make LIKE fully Unicode-aware by default?
Mainly to keep the core SQLite library small, dependency-free, and fast. Full Unicode case-folding requires substantial locale and character-mapping data, which the ICU extension provides as an optional add-on rather than baking it into the lightweight default build that most SQLite deployments rely on.
Can I check whether the ICU extension is loaded in my current SQLite build?
You can test this practically by attempting a query that relies on ICU-specific behavior (like case-insensitive matching of accented characters) and observing whether it behaves as expected. Alternatively, check your specific language binding or SQLite distribution’s documentation, since ICU support isn’t universal and often needs to be compiled in or loaded explicitly as a runtime extension.
LIKE vs. GLOB vs. Column Collation: Choosing the Right Tool
Because there are actually three different mechanisms in SQLite that touch on case sensitivity for text matching, it’s worth laying them out side by side so you can pick the right one deliberately rather than by trial and error.
LIKE with the case_sensitive_like PRAGMA gives you connection-wide control over whether % and _ wildcard pattern matching treats letter case as significant. It’s the most flexible option for ad hoc queries, but its state isn’t queryable and must be reapplied per connection, which makes it easy to lose track of in larger codebases.
GLOB is always case-sensitive, uses Unix-style wildcards (* and ? instead of % and _), and is completely unaffected by any PRAGMA setting. If you need guaranteed, connection-independent case-sensitive matching, GLOB removes an entire category of potential configuration mistakes, since there’s no setting to forget to apply.
Column-level COLLATE NOCASE takes a different approach entirely: instead of controlling matching behavior at query time, it’s baked into the column’s definition itself, affecting not just LIKE but also = comparisons and ORDER BY sorting consistently, everywhere that column is used. This is often the cleanest solution for columns that should always behave in a case-insensitive way, like email addresses or usernames in systems where “Alice@example.com” and “alice@example.com” should be treated as the same value everywhere, not just in pattern matching.
A practical rule of thumb: use case_sensitive_like when you need to toggle behavior situationally for ad hoc search queries; use GLOB when you need airtight, guaranteed case sensitivity regardless of configuration; and use COLLATE NOCASE at the schema level when a column’s case-insensitivity is a permanent, structural property of your data model rather than a per-query decision.
One more thing worth internalizing before you go: case sensitivity decisions tend to have downstream effects on user experience that are easy to overlook during development but become obvious the moment real users start typing into a search box. Someone searching for “iphone” almost certainly expects to find results tagged “iPhone,” and a case-sensitive search that fails to match will feel broken to them, even though it’s technically working exactly as configured. When in doubt, default to case-insensitive behavior for anything user-facing, and reserve case-sensitive matching for the specific, deliberate scenarios — exact identifier lookups, security tokens, data validation — where case genuinely carries meaning and where matching the wrong variant could have real consequences.
Wrapping Up
The case_sensitive_like PRAGMA is a small but genuinely useful tool for fine-tuning how your text searches behave. Most of the time, the default case-insensitive matching is exactly what you want for user-facing search. But when you need precision — matching exact-case identifiers, building security-sensitive lookups, or validating data formatting — this PRAGMA gives you a straightforward way to flip that behavior on a per-connection basis.
Just remember its limits: it only affects LIKE, it only covers ASCII case folding without the ICU extension, and it can’t be queried back once set. Keep those boundaries in mind, along with the alternative tools like GLOB and column-level COLLATE NOCASE sitting right alongside it, and you’ll be well equipped to handle case sensitivity correctly in virtually any SQLite project you take on.
