SQLite LIKE and Wildcards

The LIKE operator searches for patterns inside text values. Instead of matching exact strings, LIKE matches text that follows a pattern you define using wildcard characters. It powers search features, name lookups, and prefix matching in applications.

The Two Wildcards

WILDCARD   MEANING                     ANALOGY
─────────────────────────────────────────────────────────
%          Match any sequence of       * in file searches
           characters (including none)   (*.txt)

_          Match exactly one           ? in file searches
           any character               (file?.txt)

Sample Table

employees
─────────────────────────────────────
id │ name        │ email
───┼─────────────┼──────────────────
1  │ Alice Smith │ alice@company.com
2  │ Bob Allen   │ bob@gmail.com
3  │ Alan Brown  │ alan@company.com
4  │ Carol White │ carol@gmail.com
5  │ Alicia Chen │ alicia@work.net

Percent Wildcard (%)

Starts With — "Al%"

SELECT name FROM employees WHERE name LIKE 'Al%';

name
──────────────
Alice Smith
Alan Brown
Alicia Chen

Pattern 'Al%' means: starts with "Al", anything after.

Ends With — "%Smith"

SELECT name FROM employees WHERE name LIKE '%Smith';

name
────────────
Alice Smith

Pattern '%Smith' means: anything before "Smith".

Contains — "%com%"

SELECT name, email FROM employees WHERE email LIKE '%company%';

name         │ email
─────────────┼──────────────────
Alice Smith  │ alice@company.com
Alan Brown   │ alan@company.com

Pattern '%company%' means: "company" anywhere in the value.

Underscore Wildcard (_)

-- Match any 3-letter word followed by specific text
SELECT name FROM employees WHERE name LIKE 'Al___';

'Al___' = Al + exactly 3 more characters

Matches: Alice (Al+ice = 5 chars ✓)
         Alan  (Al+an  = 4 chars ✗ — only 2 after Al)

-- Exact-length matching example:
-- Find emails where the domain starts with exactly 4 chars
SELECT email FROM employees WHERE email LIKE '____@%';

alice → 5 chars ✗
bob   → 3 chars ✗
alan  → 4 chars ✓ → alan@company.com

Combining % and _

-- Name where second character is 'l'
SELECT name FROM employees WHERE name LIKE '_l%';

name
──────────────
Alice Smith   ← A-l-ice
Alan Brown    ← A-l-an
Alicia Chen   ← A-l-icia

Pattern '_l%' = one char + 'l' + anything

NOT LIKE

-- Employees NOT using gmail
SELECT name, email FROM employees WHERE email NOT LIKE '%gmail%';

name         │ email
─────────────┼──────────────────
Alice Smith  │ alice@company.com
Alan Brown   │ alan@company.com
Alicia Chen  │ alicia@work.net

Case Sensitivity

By default, SQLite LIKE is case-insensitive for ASCII letters (a–z). It treats uppercase and lowercase as the same for the Latin alphabet.

-- Both return Alice Smith:
SELECT name FROM employees WHERE name LIKE 'alice%';
SELECT name FROM employees WHERE name LIKE 'ALICE%';

-- Note: case-insensitivity applies to ASCII letters only.
-- Unicode letters (like accented characters) are case-sensitive
-- unless you use ICU-enabled builds.

Escaping Wildcards with ESCAPE

When you need to search for a literal percent sign or underscore in your data, use the ESCAPE clause to treat those characters as literals.

-- Search for products containing "50%"
-- Without ESCAPE, the % would act as a wildcard
SELECT name FROM discounts WHERE description LIKE '%50\%%' ESCAPE '\';

-- The \% is treated as literal %, not a wildcard.

LIKE Pattern Quick Reference

PATTERN         MATCHES
─────────────────────────────────────────────────────────
'Alice'         Exactly "Alice"
'Al%'           Starts with Al
'%son'          Ends with son
'%@gmail.com'   Ends with @gmail.com
'%art%'         Contains "art"
'A_ice'         A + one char + ice  (e.g. Alice, Atice)
'___'           Exactly 3 characters
'A%@%.com'      Starts with A, contains @, ends with .com

Performance Note

Leading % patterns like '%word' or '%word%' cannot use an index. SQLite must scan every row. Prefix patterns like 'word%' can use an index and run much faster on large tables.

FAST (can use index):     'Alice%'
SLOW (full table scan):   '%Alice'  or  '%Alice%'

Key Takeaways

• % matches zero or more of any characters
• _ matches exactly one character of any kind
• LIKE is case-insensitive for ASCII letters in SQLite
• Use NOT LIKE to exclude rows matching a pattern
• Leading wildcards prevent index use — prefix patterns are faster for large tables