Skip to main content

LIKE Operator

LIKE is used for basic pattern matching in text.

It is useful for search boxes, quick filters, and partial matches.

Core Concepts​

flowchart TD
  A[Text value] --> B[LIKE pattern]
  B --> C{Matches wildcard rules?}
  C -->|Yes| D[Row returned]
  C -->|No| E[Row excluded]
WildcardMeaningExample
%Any number of characters'A%'
_Exactly one character'J_n'

Code Examples​

-- Setup table for LIKE examples.
CREATE TABLE people_like (
  person_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);

INSERT INTO people_like (name)
VALUES ('Anna'), ('Anil'), ('Ben'), ('Joan');
Expected output
Table created and four rows inserted.
-- Prefix match: names starting with 'An'.
SELECT name
FROM people_like
WHERE name LIKE 'An%'
ORDER BY name;
name
Anil
Anna
-- Single-character wildcard.
SELECT name
FROM people_like
WHERE name LIKE 'J_an';
name
Joan

SQLite-Specific Nuances​

SQLite Nuance

By default, LIKE is case-insensitive for ASCII characters in SQLite.

Behavior can vary with collation settings and non-ASCII text.

Common Pitfalls / Best Practices​

Pitfall

Using leading wildcard patterns ('%term') everywhere, which can be slower on large datasets.

Best Practice

Prefer prefix patterns ('term%') when possible and keep search behavior consistent in the app.

Quick Challenge​

Find names in people_like that end with na.

View Solution
SELECT name
FROM people_like
WHERE name LIKE '%na';
name
Anna