SQLite LIKE: Wildcards, Case Sensitivity and ESCAPE (Tested Examples)

Ninad Pathak

October 7, 2026

Finding text that contains a word, or starts with one, is a job for LIKE, since = only finds exact matches. It has two wildcards, and two behaviours trip people up: how it handles case, and how to match a literal % sign. In this article I’ll walk you through LIKE with real output, including both.

TLDR

column LIKE 'pattern' matches text against a pattern. % matches any run of characters (even none). _ matches exactly one.
LIKE ignores case for ASCII letters only. 'a' LIKE 'A' is true, but 'é' LIKE 'É' is false.

SQLite has no ILIKE. Use LIKE for case-insensitive matching and GLOB for case-sensitive matching.
To match a literal % or _, add an ESCAPE character: LIKE '%10\%%' ESCAPE '\'.
A pattern that starts with % can’t use an index.

What is the SQLite LIKE operator?

LIKE is a comparison operator that tests a string against a pattern and returns 1 (true) or 0 (false). You’ll see it in a WHERE clause, where it filters the rows a SELECT returns. It works the same way in an UPDATE or DELETE. The SQLite expression docs define the two wildcards:

Wildcard	Matches	Example	Matches
`%`	Any sequence of zero or more characters	`'apple%'`	`Apple Watch`, `apple`
`_`	Exactly one character	`'AP-_001'`	`AP-1001`, `AP-2001`

SQLite LIKE syntax

expression [NOT] LIKE pattern [ESCAPE escape_character]

NOT LIKE returns the rows that don’t match. ESCAPE names one character that makes the next % or _ in the pattern literal.

How to filter rows with LIKE

To show you how this works, I will use a small products table:

sqlite> CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, sku TEXT NOT NULL, note TEXT);
sqlite> INSERT INTO products (name, sku, note) VALUES ('Apple iPhone 16', 'AP-1001', 'phone'), ('Apple Watch', 'AP-2001', '10% off'), ('Samsung Galaxy S25', 'SA-1001', 'phone'), ('Pineapple slicer', 'KI-0042', NULL), ('USB-C cable', 'AC-0007', '100% cotton_braided'), ('Éclair mould', 'KI-0050', NULL);

Step 1: Decide where the text can appear

Put % where any text is allowed. 'apple%' means “starts with apple”, '%cable' means “ends with cable”, and '%apple%' means “contains apple”.

Step 2: Put the pattern in the WHERE clause

sqlite> SELECT name FROM products WHERE name LIKE 'apple%';
name
---------------
Apple iPhone 16
Apple Watch

The pattern is lower case, and both rows start with a capital A. They still match, because LIKE ignores the case of ASCII letters.

Examples

These examples run against the same products table.

Match text that ends with a pattern

sqlite> SELECT name FROM products WHERE name LIKE '%cable';
name
-----------
USB-C cable

Match text that contains a pattern

sqlite> SELECT name FROM products WHERE name LIKE '%apple%';
name
----------------
Apple iPhone 16
Apple Watch
Pineapple slicer

Pineapple slicer matches too, because apple appears inside the word. If you only want whole words, LIKE isn’t enough. See SQLite full-text search.

Match one unknown character with _

Each _ stands for exactly one character:

sqlite> SELECT sku FROM products WHERE sku LIKE 'AP-_001';
sku
-------
AP-1001
AP-2001

The pattern '__-____' has two underscores before the dash and four after it, so it matches every SKU of that shape:

sqlite> SELECT sku FROM products WHERE sku LIKE '__-____';
sku
-------
AP-1001
AP-2001
SA-1001
KI-0042
AC-0007
KI-0050

Exclude matches with NOT LIKE

sqlite> SELECT name FROM products WHERE name NOT LIKE '%apple%';
name
------------------
Samsung Galaxy S25
USB-C cable
Éclair mould

Use LIKE with a parameter from your code

Build the pattern in your code and pass it as a parameter, never by pasting user input into the SQL. In Python:

import sqlite3

con = sqlite3.connect("t.db")
term = "apple"
rows = con.execute("SELECT name FROM products WHERE name LIKE ? ORDER BY id", (f"%{term}%",)).fetchall()
print(rows)

$ python3 like_param.py
[('Apple iPhone 16',), ('Apple Watch',), ('Pineapple slicer',)]

Match numbers

LIKE converts numbers to text before it compares them:

sqlite> SELECT 2026 LIKE '20%' AS int_match, 3.5 LIKE '3._' AS real_match;
int_match  real_match
---------  ----------
1          1

For number ranges, BETWEEN is clearer and can use an index.

Edge cases

Each of these comes from the test run above.

Case sensitivity stops at ASCII

The SQLite docs say “SQLite only understands upper/lower case for ASCII characters by default”:

sqlite> SELECT 'a' LIKE 'A' AS ascii, 'é' LIKE 'É' AS accented;
ascii  accented
-----  --------
1      0
sqlite> SELECT name FROM products WHERE name LIKE 'éclair%';

The lower-case éclair% pattern doesn’t find Éclair mould. For full Unicode case folding, SQLite needs the ICU extension.

There’s no ILIKE, so use GLOB for case-sensitive matching

PostgreSQL’s ILIKE doesn’t exist in SQLite:

sqlite> SELECT name FROM products WHERE name ILIKE 'apple%';
Error: in prepare, near "ILIKE": syntax error
  SELECT name FROM products WHERE name ILIKE 'apple%';
                         error here ---^

LIKE already ignores case. When you want case to matter, GLOB is case sensitive and uses * and ? as its wildcards. See SQLite GLOB:

sqlite> SELECT name FROM products WHERE name GLOB 'Apple*';
name
---------------
Apple iPhone 16
Apple Watch
sqlite> SELECT name FROM products WHERE name GLOB 'apple*';

PRAGMA case_sensitive_like = ON also makes LIKE case sensitive, but SQLite marks that pragma as deprecated (PRAGMA).

NULL never matches, even with NOT LIKE

NULL LIKE … and NULL NOT LIKE … are both NULL, so rows with a NULL note disappear from both results:

sqlite> SELECT name, note FROM products WHERE note NOT LIKE '%phone%';
name         note
-----------  -------------------
Apple Watch  10% off
USB-C cable  100% cotton_braided
sqlite> SELECT name, note FROM products WHERE note IS NULL OR note NOT LIKE '%phone%';
name              note
----------------  -------------------
Apple Watch       10% off
Pineapple slicer
USB-C cable       100% cotton_braided
Éclair mould

Add IS NULL OR when you want them back, as in the second query.

Matching a literal % or _

Here '%10%%' is meant to find “10%”, but it also matches “100%”, because the second % is a wildcard:

sqlite> SELECT name, note FROM products WHERE note LIKE '%10%%';
name         note
-----------  -------------------
Apple Watch  10% off
USB-C cable  100% cotton_braided

ESCAPE '\' makes \% a literal percent sign and \_ a literal underscore:

sqlite> SELECT name, note FROM products WHERE note LIKE '%10\%%' ESCAPE '\';
name         note
-----------  -------
Apple Watch  10% off
sqlite> SELECT name, note FROM products WHERE note LIKE '%cotton\_%' ESCAPE '\';
name         note
-----------  -------------------
USB-C cable  100% cotton_braided

LIKE and indexes

An ordinary index on name doesn’t help LIKE, because LIKE ignores case and the index doesn’t. The plan says SCAN:

sqlite> CREATE INDEX idx_products_name ON products(name);
sqlite> EXPLAIN QUERY PLAN SELECT name FROM products WHERE name LIKE 'Apple%';
QUERY PLAN
`--SCAN products USING COVERING INDEX idx_products_name

SQLite’s LIKE optimization needs an index with COLLATE NOCASE while LIKE is case-insensitive. With one, a prefix pattern becomes a range SEARCH, but a pattern that starts with % still scans:

sqlite> CREATE INDEX idx_products_name_nocase ON products(name COLLATE NOCASE);
sqlite> EXPLAIN QUERY PLAN SELECT name FROM products WHERE name LIKE 'Apple%';
QUERY PLAN
`--SEARCH products USING COVERING INDEX idx_products_name_nocase (name>? AND name<?)
sqlite> EXPLAIN QUERY PLAN SELECT name FROM products WHERE name LIKE '%Apple%';
QUERY PLAN
`--SCAN products USING COVERING INDEX idx_products_name_nocase

Conclusion

Use LIKE with % and _ for simple case-insensitive matching on ASCII text. Switch to GLOB when case matters, and add ESCAPE when the text itself contains % or _. For “contains” searches on large tables, a leading % always reads every row, so full-text search is the better tool.