The LIKE Clause in SQLite: A Complete Guide

If there’s one pattern-matching tool in SQL that I’ve used more than any other, it’s LIKE. Whether I’m building a search box for a web app, filtering messy imported data, or just poking around a database trying to find records that “sort of” match something I remember, LIKE is almost always my first move. It’s simple, forgiving, and works exactly the way most people intuitively expect text searching to work. In this guide, I’ll cover everything from the basic syntax to the more subtle behaviors that can trip you up if you’re not paying attention.

What LIKE Does

LIKE is an operator used in a WHERE clause to filter rows based on whether a text column matches a specified pattern. Unlike an exact equality check (=), LIKE supports wildcard characters that let you match partial strings — prefixes, suffixes, substrings, or patterns with placeholder characters.

The basic syntax is:

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

For example, if I want to find every customer whose name starts with “Sam”:

SELECT name FROM customers WHERE name LIKE 'Sam%';

This would match “Sam,” “Samantha,” “Samuel,” and so on.

LIKE Wildcard Characters

LIKE supports exactly two wildcard characters:

• % matches any sequence of zero or more characters
• _ matches exactly one character

Let’s look at each in detail.

The Percent Wildcard (%)

The percent sign is the workhorse of LIKE patterns. It matches any number of characters, including none at all.

-- Names starting with "Sam"
SELECT name FROM customers WHERE name LIKE 'Sam%';

-- Names ending with "son"
SELECT name FROM customers WHERE name LIKE '%son';

-- Names containing "ann" anywhere in the string
SELECT name FROM customers WHERE name LIKE '%ann%';

-- Exact match with no wildcards effectively behaves like "="
SELECT name FROM customers WHERE name LIKE 'Sam';

That last example matches only the exact string “Sam,” with no additional characters before or after — functionally similar to name = 'Sam', except with LIKE’s case-insensitivity applied.

The Underscore Wildcard (_)

The underscore matches exactly one character, no more and no less.

-- Match 4-letter codes starting with "A" and ending with "1"
SELECT code FROM inventory WHERE code LIKE 'A__1';

This pattern requires exactly two characters between the “A” and the “1” — so it would match “AXY1” or “A121” but not “A1” (too short) or “AXYZ1” (too long).

You can combine both wildcards in a single pattern:

-- Names with "a" as the second letter
SELECT name FROM customers WHERE name LIKE '_a%';

LIKE Is Case-Insensitive (for ASCII)

One of the most important things to understand about LIKE in SQLite is that, by default, it performs case-insensitive matching for ASCII characters.

SELECT name FROM customers WHERE name LIKE 'sam%';

This matches “Sam,” “SAMUEL,” “samantha,” and any other casing variation, because LIKE doesn’t care about the case of letters A through Z. This is genuinely convenient for most user-facing search features, since users typing into a search box rarely think about capitalization.

There’s an important caveat here specific to SQLite: this case-insensitivity applies only to ASCII characters (A-Z, a-z) by default. Unicode characters outside the basic ASCII range (accented letters, non-Latin scripts, etc.) are NOT case-folded unless you’ve loaded the ICU extension, which provides full Unicode-aware case folding. Out of the box, a search for 'café%' won’t necessarily match “Café” the same way it matches “cafe” vs “CAFE” for plain ASCII letters — this is a subtlety worth remembering if your application deals with international text.

If you specifically want case-sensitive matching in SQLite, you have two options:

1. Use the GLOB operator instead, which is case-sensitive by default.
2. Enable the case_sensitive_like pragma:

PRAGMA case_sensitive_like = ON;

Once this pragma is set, LIKE comparisons in that database connection become case-sensitive, matching character-for-character including case. Keep in mind this pragma affects the entire connection, not just a single query, so it’s not something you’d typically toggle mid-application without being very deliberate about it.

Escaping Wildcard Characters

Sometimes the text you’re searching for actually contains a literal % or _ character — for example, searching for a discount code like “50%OFF” or a database column literally named with an underscore. Since % and _ are special wildcard characters in LIKE patterns, you need a way to tell SQLite “treat this character literally, not as a wildcard.”

That’s what the ESCAPE clause is for:

SELECT description FROM promotions
WHERE description LIKE '%50\%%' ESCAPE '\';

Here, I’ve defined the backslash (\) as my escape character. In the pattern, \% means “a literal percent sign,” while the surrounding % characters (without a preceding backslash) still act as normal wildcards. So this pattern matches any string that contains the literal substring “50%” somewhere within it.

You can choose any character as your escape character, as long as you declare it explicitly:

SELECT code FROM inventory
WHERE code LIKE 'A#_1%' ESCAPE '#';

Here, #_ means “a literal underscore character,” not the single-character wildcard.

NOT LIKE

Just as with most SQL operators, you can negate LIKE using NOT:

SELECT name FROM customers WHERE name NOT LIKE 'Test%';

This is genuinely useful for filtering out known junk data — test accounts, placeholder entries, or known bad records that follow a predictable naming pattern.

LIKE with NULL Values

It’s worth calling out explicitly: if the column being compared contains NULL, a LIKE comparison (in either direction, positive or NOT LIKE) will evaluate to NULL, not true or false, and the row will be excluded from the results either way. This trips people up because they sometimes expect NOT LIKE to catch NULL rows along with non-matching rows, but it won’t — NULL rows require an explicit IS NULL check if you want to include them.

SELECT name FROM customers
WHERE name NOT LIKE 'Test%' OR name IS NULL;

Combining LIKE with AND/OR

LIKE combines naturally with other conditions using AND and OR, just like any boolean expression.

SELECT name, email
FROM customers
WHERE name LIKE 'J%' AND email LIKE '%@gmail.com';

This finds customers whose name starts with “J” and whose email is a Gmail address. You can chain multiple LIKE conditions together for more complex searches:

SELECT product_name
FROM products
WHERE product_name LIKE '%wireless%'
   OR product_name LIKE '%bluetooth%';

Practical Real-World Examples

Building a Simple Search Feature

If you’re building a basic search box for a small-to-medium app, a straightforward LIKE query often does the job without needing a dedicated full-text search engine:

SELECT title, author
FROM books
WHERE title LIKE '%' || :search_term || '%'
   OR author LIKE '%' || :search_term || '%';

Here I’m using SQLite’s || string concatenation operator to build the pattern dynamically from a bound parameter, wrapping the user’s search term with % on both sides to find it anywhere in the title or author field.

Filtering Email Domains

SELECT email FROM users WHERE email LIKE '%@company.com';

Finding Records with Missing or Placeholder Data

SELECT * FROM orders WHERE notes LIKE '%TODO%' OR notes LIKE '%FIXME%';

Matching Fixed-Length Codes

SELECT * FROM inventory WHERE sku LIKE '___-____';

This finds SKUs matching a 3-character prefix, a hyphen, and a 4-character suffix — useful for validating or filtering structured identifiers.

LIKE and Indexes

Just like GLOB, LIKE can use an index under specific conditions. If the pattern is left-anchored — meaning it starts with a fixed sequence of characters rather than a wildcard — SQLite can use a B-tree index to jump directly to the relevant range of rows.

CREATE INDEX idx_customers_name ON customers(name);

-- Can use the index (left-anchored pattern)
SELECT * FROM customers WHERE name LIKE 'Sam%';

-- Cannot use the index (leading wildcard)
SELECT * FROM customers WHERE name LIKE '%Sam%';

There’s a subtlety specific to SQLite here worth knowing: because LIKE is case-insensitive by default, and standard SQLite indexes use binary (case-sensitive) collation, SQLite actually can’t use a normal index for a case-insensitive LIKE match unless the index was built with the NOCASE collation, or unless case_sensitive_like has been turned on.

CREATE INDEX idx_customers_name_nocase ON customers(name COLLATE NOCASE);

With this index in place, a query like WHERE name LIKE 'sam%' can use the index efficiently even though the pattern’s case doesn’t match the underlying data’s case. This is a genuinely important performance detail that a lot of people miss, since a “left-anchored” pattern alone isn’t sufficient for index usage under LIKE’s default case-insensitive behavior — you also need the collation to line up.

You can confirm actual index usage with:

EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE name LIKE 'Sam%';

LIKE vs. GLOB vs. REGEXP

Since I’ve written about GLOB elsewhere, it’s worth a quick comparison here for context:

Feature	LIKE	GLOB
Case sensitivity	Insensitive (ASCII) by default	Sensitive by default
Multi-char wildcard	%	*
Single-char wildcard	_	?
Character classes	Not supported	Supported ([abc], [a-z])
Escape clause	Supported (ESCAPE)	Not supported natively

If you need true regular expressions — alternation, repetition quantifiers, lookaheads, and so on — neither LIKE nor GLOB will get you there natively. SQLite supports a REGEXP operator, but it requires either the optional REGEXP extension to be loaded, or a custom REGEXP function registered at the application layer, since there’s no built-in regex engine compiled in by default.

Common Mistakes to Avoid

1. Forgetting that LIKE is case-insensitive by default, and being surprised when a query intended to be exact-case still matches other casings.
2. Not escaping literal % or _ characters when searching for text that actually contains them, leading to incorrect matches.
3. Assuming NOT LIKE will exclude NULL rows — it won’t, since any comparison against NULL evaluates to NULL, not true.
4. Using leading wildcards (%text) on large tables without realizing this forces a full table scan, hurting performance.
5. Forgetting the collation mismatch issue — expecting an index to be used for a case-insensitive LIKE query when the index itself was built with default binary collation.

Best Practices

• Use % and _ deliberately, and always double-check whether your pattern needs to be anchored at the start, end, both, or neither.
• Always use the ESCAPE clause when your search term might contain literal wildcard characters, especially in dynamic, user-generated search queries.
• Build a COLLATE NOCASE index on any column you frequently filter with LIKE, to make sure your case-insensitive searches can actually use the index.
• Avoid leading-wildcard patterns on large, frequently-queried tables unless you’ve accepted the performance trade-off or paired it with a proper full-text search solution.
• For genuinely advanced text search needs (ranking, stemming, fuzzy matching), consider SQLite’s FTS5 extension rather than stretching LIKE beyond what it’s meant for.

LIKE is one of those clauses that feels almost too simple to write a whole guide about — until you hit one of its quirks in production and lose an afternoon figuring out why your “obvious” query isn’t behaving the way you expected. Understanding its case-insensitivity, wildcard rules, escaping mechanism, and indexing behavior will save you from exactly that kind of afternoon.

Frequently Asked Questions

Is LIKE standard SQL, unlike GLOB? Yes — LIKE is part of the ANSI SQL standard and is supported across virtually every relational database (MySQL, PostgreSQL, SQL Server, Oracle), which is exactly why it tends to be the default choice for portable pattern-matching queries. GLOB, by contrast, is a SQLite-specific extension.

Can I use LIKE on numeric columns? Yes, though SQLite will implicitly convert the number to text before comparison, based on type affinity. As with GLOB, I prefer being explicit with CAST(column AS TEXT) when applying LIKE to a numeric column, just to keep the intent clear.

SELECT * FROM orders WHERE CAST(order_id AS TEXT) LIKE '100%';

Does LIKE support Unicode characters correctly? Partially. LIKE works fine at matching Unicode characters literally, but its case-insensitivity only applies to standard ASCII letters (A-Z) by default. If your data includes accented characters or non-Latin scripts and you need proper Unicode-aware case folding, you’d need to load the ICU extension, which most default SQLite builds don’t include out of the box.

How do I search for an exact literal % character in my data? Use the ESCAPE clause, as covered earlier in this guide:

SELECT * FROM promotions WHERE code LIKE '%\%%' ESCAPE '\';

Is there a performance difference between LIKE and = for exact matches? For a pattern with no wildcards at all (like WHERE name LIKE 'Sam'), LIKE behaves very similarly to =, except that it still applies its case-insensitivity rules, while = performs a strict binary comparison. If you genuinely need an exact, case-sensitive match with no wildcard behavior at all, = is both clearer in intent and marginally more efficient, since it skips LIKE’s pattern-matching machinery entirely.

Building a Multi-Field Search with LIKE

A pattern I use constantly when building lightweight search functionality for smaller applications — where a full dedicated search engine like FTS5 would be overkill — is searching across multiple columns simultaneously using LIKE combined with OR:

SELECT * FROM contacts
WHERE first_name LIKE '%' || :query || '%'
   OR last_name LIKE '%' || :query || '%'
   OR email LIKE '%' || :query || '%'
   OR phone LIKE '%' || :query || '%';

For small to medium datasets (a few thousand to low tens of thousands of rows), this kind of query performs perfectly well even without dedicated indexing, especially if it’s not running on every keystroke but rather on a debounced search input or an explicit “search” button click.

LIKE for Data Validation and Cleanup

Beyond search features, I use LIKE constantly for one-off data-quality investigations — spotting malformed data, inconsistent formatting, or leftover test records in a table I’m inheriting or auditing.

-- Find email addresses missing an "@" symbol (likely malformed)
SELECT * FROM users WHERE email NOT LIKE '%@%';

-- Find phone numbers that don't match a expected pattern
SELECT * FROM contacts WHERE phone NOT LIKE '___-___-____';

-- Find obviously fake or placeholder entries
SELECT * FROM customers WHERE name LIKE '%test%' OR name LIKE '%example%';

These kinds of exploratory LIKE queries are usually the very first thing I run when I’m handed a new, unfamiliar dataset and asked to assess its quality before doing anything more serious with it.

LIKE Inside CASE Expressions

LIKE composes naturally inside CASE expressions for building categorization logic based on partial text matches:

SELECT
    email,
    CASE
        WHEN email LIKE '%@gmail.com' THEN 'Gmail'
        WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'
        WHEN email LIKE '%@outlook.com' OR email LIKE '%@hotmail.com' THEN 'Microsoft'
        ELSE 'Other'
    END AS email_provider
FROM users;

This is a genuinely handy pattern for quick analytics — bucketing free-text or semi-structured data into meaningful categories directly within a query, without needing a separate lookup table or post-processing step in application code.

When LIKE Isn’t Enough: Moving to Full-Text Search

If you find your application’s search needs growing beyond what LIKE can comfortably or efficiently handle — ranking results by relevance, searching across large volumes of text, handling stemming or partial-word matches intelligently — SQLite’s FTS5 extension is specifically designed for exactly that. FTS5 builds a proper inverted index over your text columns, which makes it dramatically faster than a leading-wildcard LIKE query on large tables, and it supports relevance ranking, phrase matching, and boolean text queries that LIKE simply isn’t built for.

CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);

SELECT * FROM articles_fts WHERE articles_fts MATCH 'database AND performance';

I mention this here mainly so it’s clear that LIKE has a natural ceiling — it’s perfect for simple, small-to-medium-scale pattern matching, but once search becomes a first-class feature of your application, FTS5 is almost always the better long-term investment.