You usually notice you need pattern matching at the most annoying moment: a support ticket says a device can’t find a saved Wi‑Fi network, a log viewer needs a quick filter, or a settings screen should search “cam” and show “Camera”, “Camcorder”, and “Campus Mode”. In embedded and edge apps, I often reach for SQLite because it’s serverless (no daemon, no setup), tiny, and dependable—especially when it’s compiled into a product written in C/C++ and shipped onto TVs, cameras, kiosks, or routers.
When you don’t have the full string you’re looking for, SQLite’s LIKE operator gives you a fast, readable way to match patterns in text columns. It’s not a full search engine, and it has sharp edges (escaping, case rules, index behavior), but it’s perfect for a large class of “find me rows that look like this” problems.
I’m going to show you how LIKE works, what the wildcards really mean, how to match literal % and _ safely, and what I do in production to keep these queries correct and fast. You’ll also see runnable examples you can paste into the sqlite3 CLI and small application snippets for Python and Node.js.
Why LIKE still matters in 2026 SQLite apps
SQLite has grown a lot in the past decade, but the basic shape of embedded data problems hasn’t changed: you keep a small-to-medium dataset on-device, you need predictable behavior offline, and you want queries that are simple enough to reason about when debugging a field issue.
LIKE earns its keep because it sits in the sweet spot between exact matches and heavyweight search:
- You get pattern matching directly in SQL without extra extensions.
- It’s easy to parameterize safely from application code.
- With the right pattern (especially prefix matches), it can use an index and stay quick.
- It’s readable during incident response: anyone who knows SQL can understand it.
Where I do not start with LIKE:
- “Search across paragraphs of user content” (use FTS5).
- “Match Unicode case-folding correctly for every language” (you need a more deliberate plan, often FTS or app-side normalization).
- “Fuzzy matching / typos” (again: FTS + tokenization, or a dedicated fuzzy approach).
Think of LIKE as a stencil: you cut out a simple shape (the pattern), then check which strings fit through it.
The mental model: patterns, wildcards, and what LIKE actually compares
At its core, the operator looks like this:
SELECT col1, col2
FROM your_table
WHERE sometextcolumn LIKE pattern;
The “pattern” is a string that may include wildcard characters:
%matches zero or more characters_matches exactly one character
That’s the entire wildcard vocabulary for LIKE. No character classes, no *, no ?. If you need richer patterns, SQLite also has GLOB (Unix-style patterns) and regular expression support only if you provide an extension (SQLite does not ship a built-in REGEXP operator).
A few rules I keep in mind:
1) LIKE is usually used on text columns (TEXT), but it can also be applied to values that SQLite can coerce to text.
2) LIKE is not the same as substring search by default. Substring search is a pattern shape you choose:
- Prefix match:
‘cam%‘ - Suffix match:
‘%cam‘ - Contains:
‘%cam%‘
3) The query planner behavior depends heavily on where % appears. A leading % often prevents index range scans.
4) Case sensitivity is tricky. By default, LIKE is typically case-insensitive for ASCII letters in many SQLite builds, but you should not treat that as a universal truth across deployments. I’ll show you how to make case behavior explicit.
A runnable dataset you can reuse
Open the SQLite CLI and paste this whole block (it’s designed to be copy/paste friendly):
— Create a small, realistic table.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
emp_name TEXT NOT NULL,
dept TEXT NOT NULL,
email TEXT,
note TEXT
);
INSERT INTO employees (emp_name, dept, email, note) VALUES
(‘Ravi Kumar‘, ‘IT Support‘, ‘[email protected]‘, ‘On-callweek1‘),
(‘Sara Nguyen‘, ‘IT Security‘, ‘[email protected]‘, ‘Rotates%coverage‘),
(‘Marta Silva‘, ‘Accounting‘, ‘[email protected]‘, ‘Handles Q4 close‘),
(‘Hana Lee‘, ‘IT Operations‘, ‘[email protected]‘, ‘Owns camera fleet‘),
(‘Sam Patel‘, ‘Facilities‘, ‘[email protected]‘, ‘Badge access‘),
(‘Amit Sharma‘, ‘Analytics‘, ‘[email protected]‘, ‘Builds dashboards‘),
(‘Kate Johnson‘, ‘IT Support‘, ‘[email protected]‘, ‘Trains new hires‘),
(‘Ibrahim Hassan‘, ‘IT Operations‘, ‘[email protected]‘, ‘Escapes underscore: namehas_two‘);
Now you can test each LIKE pattern without changing anything else.
Percent (%): prefix, suffix, and contains patterns
% matches any run of characters, including an empty run. That “including empty” detail matters more than people expect.
Prefix match: the most index-friendly pattern
This is the pattern I reach for first, especially for UI “typeahead” search:
SELECT emp_name, dept
FROM employees
WHERE dept LIKE ‘IT%‘
ORDER BY emp_name;
This returns rows where dept starts with IT (for example: IT Support, IT Security, IT Operations).
Why I like prefix matches:
- They map well to how people search (“I know how it starts”).
- They can often use an index on
dept(details in the performance section).
Contains match: easy, but can be expensive
This pattern checks whether the substring appears anywhere:
SELECT emp_name, note
FROM employees
WHERE note LIKE ‘%camera%‘
ORDER BY emp_name;
It’s expressive and correct for a lot of log/search UI needs, but you pay for it when tables grow because SQLite can’t jump into the middle of an index for ‘%camera%‘.
If you’re scanning tens of thousands of rows on-device, that might still be fine (typical ranges I see in well-built embedded UIs are “a few ms to a few tens of ms” depending on hardware). If you’re scanning millions, you should switch strategies.
Suffix match: often a warning sign
Suffix matches look like this:
SELECT emp_name, email
FROM employees
WHERE email LIKE ‘%@example.com‘
ORDER BY emp_name;
Suffix patterns are legit (email domain filters, file extensions, log suffix tags), but they tend not to be index-friendly for the same reason as contains matches.
% can match nothing: a subtle correctness trap
Because % can match zero characters, these two patterns are different:
‘IT%‘matchesITandIT Support‘IT%‘matchesITfollowed by at least one more character (becauseforces one character)
You can use that to your advantage when you need “at least one extra character” without writing awkward length checks.
Underscore (_): fixed-position matching when you care about the exact slot
_ matches exactly one character. It’s the right tool when position matters.
A simple example: “Second letter is a” in a name:
SELECT emp_name, dept
FROM employees
WHERE empname LIKE ‘a%‘
ORDER BY emp_name;
That reads as:
_= any first charactera= literala%= anything after
This kind of pattern is useful for:
- Validating codes with a known structure (for example,
AB-1234where each segment has a fixed length) - Finding data quality issues (“third character is a space”, “contains two underscores in a row”)
- Legacy identifiers where you can’t change the format but need to query it
Matching a structured code: a real-world example
Suppose you have a column device_tag like TV-CA-2048 and TV-NY-9912. You can match “TV, any state, four digits” with:
— Pattern: TV-–
— (two-letter state + four-digit number)
SELECT device_tag
FROM devices
WHERE devicetag LIKE ‘TV-–_‘;
If you need “TV plus anything” you’d switch to TV%, but when structure matters, _ keeps you honest.
Common mistake: confusing _ with “optional”
_ is never optional; it always consumes one character. If you need “optional character”, you can’t express that directly with LIKE. I usually handle it by:
- Writing two patterns and using
OR(when the list is short) - Using
GLOBor a regex extension (when pattern rules are richer) - Normalizing the data into separate columns (best long-term)
Escaping literal % and _ with ESCAPE
Sooner or later you’ll store a literal underscore or percent in a column: log tags, template placeholders, user-entered text, migration notes, or version strings.
If you write:
WHERE note LIKE ‘%_%‘
…you are not matching a literal underscore. You’re matching “any string that contains at least one character” because _ matches any single character.
SQLite’s ESCAPE clause lets you pick an escape character, then treat the next character as a literal even if it’s % or _.
General form:
SELECT …
FROM …
WHERE column LIKE pattern ESCAPE ‘\‘;
Match rows that contain a literal underscore
Using the sample dataset above, this finds notes containing _:
SELECT emp_name, note
FROM employees
WHERE note LIKE ‘%\_%‘ ESCAPE ‘\‘
ORDER BY emp_name;
Read it carefully:
- Pattern string contains
\because we want a literal backslash in SQL text and then. ESCAPE ‘\‘tells SQLite: “when you see a backslash in the pattern, interpret the next character literally.”
Match rows that contain a literal percent sign
Same idea:
SELECT emp_name, note
FROM employees
WHERE note LIKE ‘%\%%‘ ESCAPE ‘\‘
ORDER BY emp_name;
If you’ve ever built a search box that supports “find the exact string %”, this is the technique.
Application-code escaping: the place bugs breed
Two layers of escaping tend to collide:
- SQL string escaping (quotes, backslashes)
- Language string escaping (Python/JS backslashes)
My rule: if the pattern comes from user input, I do not hand-build SQL strings. I bind parameters and I build the escaped pattern in application code.
Python example (runnable):
import sqlite3
def escapelike(raw: str, escapechar: str = "\\") -> str:
# Escape the escape char first, then % and _
raw = raw.replace(escapechar, escapechar + escape_char)
raw = raw.replace("%", escape_char + "%")
raw = raw.replace("", escapechar + "_")
return raw
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE notes(text TEXT)")
conn.executemany("INSERT INTO notes(text) VALUES (?)", [
("Rotates%coverage",),
("On-callweek1",),
("Plain text",),
])
usersearch = "%" # user wants literal percent + underscore
escaped = escapelike(usersearch)
pattern = f"%{escaped}%"
rows = conn.execute(
"SELECT text FROM notes WHERE text LIKE ? ESCAPE ‘\\‘",
(pattern,),
).fetchall()
print(rows)
Node.js example (runnable with better-sqlite3):
const Database = require(‘better-sqlite3‘);
function escapeLike(raw, escapeChar = ‘\\‘) {
return raw
.replaceAll(escapeChar, escapeChar + escapeChar)
.replaceAll(‘%‘, escapeChar + ‘%‘)
.replaceAll(‘‘, escapeChar + ‘‘);
}
const db = new Database(‘:memory:‘);
db.exec(‘CREATE TABLE notes(text TEXT)‘);
const ins = db.prepare(‘INSERT INTO notes(text) VALUES (?)‘);
ins.run(‘Rotates%coverage‘);
ins.run(‘On-callweek1‘);
ins.run(‘Plain text‘);
const userSearch = ‘%_‘;
const escaped = escapeLike(userSearch);
const pattern = %${escaped}%;
const stmt = db.prepare("SELECT text FROM notes WHERE text LIKE ? ESCAPE ‘\\‘");
console.log(stmt.all(pattern));
The important parts:
- You escape
%and_in the user’s input. - You still decide the matching shape (
%...%for contains,...%for prefix). - You bind the pattern as a parameter, so you don’t create SQL injection problems.
Case sensitivity, collations, and Unicode gotchas
Case rules are where LIKE surprises people, especially across platforms.
Make case behavior explicit
If you need case-insensitive search, I generally do one of these:
1) Normalize both sides with LOWER(...) (simple, but can block index use):
SELECT emp_name
FROM employees
WHERE LOWER(emp_name) LIKE LOWER(‘sa%‘);
2) Store a normalized column (recommended when search is frequent):
— Example approach: store a lowercase copy.
— You can keep it in sync via app logic or triggers.
ALTER TABLE employees ADD COLUMN empnamelc TEXT;
UPDATE employees SET empnamelc = LOWER(emp_name);
CREATE INDEX IF NOT EXISTS idxemployeesempnamelc
ON employees(empnamelc);
SELECT emp_name
FROM employees
WHERE empnamelc LIKE ‘sa%‘;
3) Use the right collation where it applies. SQLite has NOCASE, but it’s not a full Unicode case-folding solution. It’s fine for many English-centric UIs and internal tools, and less fine when you promise “works for every language.”
Be careful with PRAGMA casesensitivelike
SQLite has a casesensitivelike pragma in many builds. Depending on how SQLite is compiled and configured, LIKE may be case-sensitive or not.
If you ship SQLite in a product (common in embedded), you should test and document the behavior you expect. I treat case rules as a release requirement: if the UI search expects case-insensitive prefix matching, I make that part of automated tests so a future SQLite upgrade or compile flag doesn’t quietly change behavior.
Unicode and accents: decide what you promise
If your app needs cafe to match café, LIKE won’t solve that by itself. You have a few realistic choices:
- Normalize and strip accents into a separate “search” column (app-side). This is often good enough and keeps queries simple.
- Use FTS5 with a tokenizer that fits your language needs.
- If you need perfect locale-aware behavior, push more of the logic into a library that supports ICU-style collation and searching (and accept the footprint).
In my experience, the “two-column approach” (raw text + normalized search text) is the best balance for many device and mobile apps.
Performance: indexes, query plans, and when to switch away from LIKE
Most LIKE performance problems come down to one thing: can SQLite turn your pattern into an index range scan, or does it have to read rows and check them one by one?
Create the right index
If you frequently search by prefix on a column, index it:
CREATE INDEX IF NOT EXISTS idxemployeesdept
ON employees(dept);
Then a query like dept LIKE ‘IT%‘ can often use that index.
Use EXPLAIN QUERY PLAN to verify
In the sqlite3 CLI:
EXPLAIN QUERY PLAN
SELECT emp_name, dept
FROM employees
WHERE dept LIKE ‘IT%‘;
You’re looking for output that indicates an index is used (wording varies). If you see a full table scan, SQLite is checking every row.
Patterns that often block index use
These frequently force scans:
- Leading wildcard:
‘%IT‘,‘%IT%‘ - Expressions on the column:
LOWER(dept) LIKE ‘it%‘(unless you indexed the expression via a generated column approach)
If you must do contains search on large tables, treat it as a sign to move to a different tool.
Better tools for “search-like” search
SQLite gives you options without leaving SQLite:
- FTS5 for full-text search (tokenized, fast for contains-like queries)
- Generated columns for normalized search keys (for predictable prefix matching)
- Triggers that maintain search columns (when you can’t use generated columns or want explicit control)
Here’s how I decide quickly:
LIKE
—
"cam" -> "camera") Best starting point
Can get slow
Manual, awkward
Minimal
Very small
If you’re building “search a list of 5,000 settings labels” on a TV, LIKE ‘cam%‘ with an index and a normalized column is usually perfect.
If you’re building “search 200,000 log lines and highlight results,” I switch to FTS5 early.
A modern pattern I like: normalized + indexed prefix search
For fast, predictable prefix search:
- Keep your original text as-is.
- Maintain
search_keyas lowercase (and possibly accent-stripped). - Index
search_key. - Query
search_key LIKE ?withterm + ‘%‘.
That gives you:
- stable case behavior
- index-friendly queries
- simple parameter binding
Safe usage patterns in real code (and the mistakes I see most)
LIKE is easy to read, but production bugs tend to cluster around four issues: parameter binding, wildcard placement, escaping, and unbounded scans.
1) Always bind parameters
Bad pattern (string concatenation):
— Don‘t do this with user input.
SELECT empname FROM employees WHERE empname LIKE ‘%" + user_input + "%‘ ;
Good pattern (parameterized):
SELECT emp_name
FROM employees
WHERE emp_name LIKE ?;
Then your app passes the pattern as a value.
2) Decide your matching shape deliberately
When I implement a search box, I choose one of these behaviors explicitly:
- Prefix search (fast, predictable):
term + ‘%‘ - Contains search (friendlier, can be slower):
‘%‘ + term + ‘%‘
A simple analogy: prefix search is like looking up a word in a dictionary by first letters; contains search is like flipping every page and checking if the word appears anywhere.
3) Don’t forget to escape % and _ for literal search
If you offer “find exact text,” and the user types %, they probably mean the percent character, not “match anything.”
This is where the escapeLike() helper from the earlier section matters.
4) Put a guardrail on expensive queries
For device UIs, I often add:
- a minimum term length for contains search (for example, require 2–3 characters)
- a limit on results (
LIMIT 50) - a timeout or cancel path in the UI thread (especially on slower CPUs)
Example:
SELECT emp_name, dept
FROM employees
WHERE empnamelc LIKE ?
ORDER BY emp_name
LIMIT 50;
That keeps “search everything with %a%” from stalling your UI.
5) Prefer LIKE over = only when you need it
I see teams use LIKE everywhere out of habit:
WHERE email LIKE ‘[email protected]‘
That should be =. Exact comparisons are clearer and let the planner use indexes without pattern rules getting in the way.
What I’d do next: a practical checklist you can apply today
If you’re adding pattern matching to an SQLite-backed app, I’d start by writing down the user experience you want: prefix search, contains search, or structured matching. Then I’d make the behavior explicit in SQL so it stays stable across refactors.
Here’s the checklist I use:
1) Pick the pattern shape on purpose.
- For typeahead and filters:
term + ‘%‘ - For “find anywhere”:
‘%‘ + term + ‘%‘(and add guardrails)
2) Make case behavior a feature, not an accident.
- If you want case-insensitive search, create a normalized column (
*_lc) and index it. - Add a test that proves
"Camera"matches"cam%"the way you expect.
3) Handle literal % and _ the moment you accept user input.
- Escape user input with a helper.
- Use
ESCAPEconsistently.
4) Verify performance with EXPLAIN QUERY PLAN.
- If your prefix searches aren’t using an index, fix that before shipping.
- If you need contains search on large text, move to FTS5 instead of trying to brute-force it.
5) Keep it safe and maintainable.
- Bind parameters.
- Use
LIMITfor UI queries. - Avoid clever patterns that future you won’t understand at 2 a.m.
If you do those five things, LIKE becomes one of those boring, reliable tools—the kind I want in embedded and edge software—because it behaves the same way every day, on every device, even when everything else is on fire.
