Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 125 · Querying Data

Pattern Matching with LIKE

Match text patterns in SQL with LIKE and the % and _ wildcards. Learn case sensitivity across databases, ESCAPE, NOT LIKE, SQLite GLOB and regular expressions.

So far your WHERE conditions have compared whole values: city = 'Lyon' matches only the exact text Lyon. Real questions are often fuzzier. Which products have "speaker" in their name? Which customers have an email at a certain domain? Which codes start with EU-? For those, SQL gives you LIKE, an operator that compares text against a pattern.

LIKE is simple, portable and available in every SQL database. In this lesson you'll learn its two wildcards, how case sensitivity differs between databases, how to search for a literal % or _, and what to reach for when a pattern is too complex for LIKE: SQLite's GLOB and the regular expressions offered by other databases.

The two wildcards: % and _

A LIKE pattern is ordinary text plus two special characters:

Wildcard Matches Example pattern Matches Doesn't match
% any sequence of characters, including none 'Fold%' Folding Camp Chair, Fold Unfolded
_ exactly one character '_ola' Lola, Nola ola, Viola

Every other character in the pattern must match itself. The whole value must match the whole pattern, so 'Speaker' without wildcards behaves almost like =.

The most common pattern is "contains", written with % on both sides. This finds every product whose name contains the word Speaker:

SQL Contains a word · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result has two rows: Bookshelf Speakers and Portable Speaker. The leading % lets any text come before "Speaker", and the trailing % lets anything (including the final "s") come after it. Remove the trailing % and run it again: only Portable Speaker is left, because now the name must end with "Speaker".

Starts with, ends with

Put the % on one side only to anchor the pattern at the start or the end of the value:

SQL
WHERE last_name LIKE 'An%'           -- starts with "An"
WHERE email LIKE '%@example.com'     -- ends with "@example.com"
WHERE name LIKE '%Set'               -- ends with "Set"

Try both directions on the shop's customers. The first query finds every customer whose last name starts with "An", the second finds products ending in "Set":

SQL Starts with and ends with · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first table shows four people named Andersson, and the second shows Silicone Spatula Set and Watercolor Set.

Matching exact lengths with _

The underscore stands for exactly one character, so a pattern made of underscores matches values of a precise length. Three underscores find every first name that is three characters long:

SQL The underscore wildcard · database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns five rows: two Ivos, two Wens and an Uma. The second pattern, '_a%', means "any one character, then a, then anything", so it lists the distinct first names whose second letter is "a" (DISTINCT removes repeats; it gets its own lesson in DISTINCT).

You can mix the wildcards freely. '%_' means "at least one character", which is a compact way to reject empty strings.

NOT LIKE

Put NOT in front of LIKE to keep the rows that do not match. The company database has employees with an email address and a few without one (their email is NULL). Watch what happens to them:

SQL NOT LIKE and NULL · database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns no rows. Every email that exists ends in @company.example, and the three employees with a NULL email aren't returned either, even though they clearly don't have a company address. Any comparison with NULL, including LIKE and NOT LIKE, gives unknown, and WHERE only keeps rows where the condition is true. The second query shows those three people. If you want them in the first result, say so explicitly:

SQL
WHERE email NOT LIKE '%@company.example'
   OR email IS NULL

You'll study this behavior properly in Working with NULL.

Case sensitivity: it depends on the database

Is 'speaker' LIKE '%Speaker%' true? The SQL standard says LIKE is case-sensitive, but databases disagree in practice:

Database Is LIKE case-sensitive? Case-insensitive option
SQLite No, for ASCII letters A–Z only (default)
PostgreSQL Yes ILIKE, or lower(x) LIKE lower(pattern)
MySQL / MariaDB Depends on the column's collation; the default collations are case-insensitive (default)
SQL Server Depends on the collation; the default is case-insensitive (default)
Oracle Yes UPPER(x) LIKE UPPER(pattern)

SQLite folds only the 26 English letters. Letters such as É and é or Ş and ş are compared exactly, so for international text a case-insensitive match isn't guaranteed. You can test this without any table, because SELECT works without FROM and a comparison returns 1 (true) or 0 (false) in SQLite:

SQL Case sensitivity in SQLite

Press Run (or Ctrl + Enter) to see the result here.

The result is 1, 0, 1 and NULL. The first match ignores case, the accented É is not folded to é, _ happily matches the accented character, and NULL LIKE anything is NULL, not false.

Make case explicit when it matters. lower(name) LIKE '%speaker%' behaves the same in every database, so it's a good habit in code that must run on more than one system. In PostgreSQL, ILIKE is shorter and can use special indexes.

Searching for a literal % or _

What if the text you're looking for contains a % or an _? A discount code such as SAVE_10 has an underscore, and LIKE 'SAVE_10' would also match SAVEX10, because _ is a wildcard. The ESCAPE clause lets you pick an escape character. A wildcard right after it loses its special meaning:

SQL ESCAPE a wildcard

Press Run (or Ctrl + Enter) to see the result here.

The first SELECT returns both SAVE_10 and SAVEX10. The second, where !_ means "a real underscore", returns only SAVE_10. The third pattern '%!%%' reads as "anything, a real percent sign, anything" and returns only 100%OFF.

Any single character can be the escape character; choose one that doesn't appear in your data. Dialects differ in the default:

  • SQLite and SQL Server have no default escape character, so use ESCAPE when you need one.
  • PostgreSQL and MySQL treat backslash as the default escape: LIKE 'SAVE\_10' works without an ESCAPE clause.
  • SQL Server also accepts brackets: LIKE 'SAVE[_]10' matches a literal underscore.

Escape user input before building a pattern. If a search box puts the user's text straight into '%' || :term || '%', typing % or _ changes the meaning of the search. Escape those characters (and the escape character itself) in application code first, and always pass the value as a query parameter. See SQL Injection and Secure Queries.

GLOB: SQLite's case-sensitive patterns

SQLite has a second pattern operator, GLOB, which uses the wildcards of a Unix shell and is always case-sensitive:

GLOB Meaning LIKE equivalent
* any sequence of characters %
? exactly one character _
[abc] one character from the set (none)
[a-z] one character from a range (none)
[^0-9] one character not in the set (none)

The character sets are what make GLOB useful. This query finds product names that contain a digit, and then shows that GLOB respects case where LIKE does not:

SQL GLOB character classes · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns Chef Knife 20 cm and Desk Planner 2026. The LIKE count is 2, but the GLOB count is 0, because no product name contains a lowercase "speaker".

GLOB is specific to SQLite. Other databases use LIKE with brackets (SQL Server) or regular expressions for the same jobs.

Regular expressions in other databases

For complex rules ("letters, then a dash, then exactly four digits") a regular expression is the right tool. SQLite understands the REGEXP keyword but ships without a function behind it, so in this browser runner x REGEXP 'pattern' fails with an error. The big server databases include regex support:

SQL
-- PostgreSQL: ~ is case-sensitive, ~* is case-insensitive
SELECT name FROM products WHERE name ~ '[0-9]{2,}';
SELECT name FROM products WHERE name ~* '^(cast|chef)';

-- PostgreSQL also supports the standard SIMILAR TO (rarely used)
SELECT name FROM products WHERE name SIMILAR TO '%[0-9]+%';

-- MySQL 8 / MariaDB
SELECT name FROM products WHERE name REGEXP '[0-9]{2,}';
SELECT name FROM products WHERE REGEXP_LIKE(name, '^(cast|chef)', 'i');

-- Oracle
SELECT name FROM products WHERE REGEXP_LIKE(name, '[0-9]{2,}');

-- SQL Server 2025 adds REGEXP_LIKE; older versions only have LIKE with [ ] sets
SELECT name FROM products WHERE name LIKE '%[0-9][0-9]%';

Before reaching for regex, check whether LIKE or GLOB already does the job: they are simpler to read and supported everywhere you need them.

LIKE and performance

A pattern that starts with a fixed prefix, such as 'An%', can use an index on the column in many databases, because the database knows where in the sorted index to start looking. A pattern that starts with a wildcard, such as '%son', cannot: the database has to read every row and test it. On a table with a few thousand rows that's instant; on a table with a hundred million rows it isn't.

Two other facts to keep in mind:

  • In SQLite, a prefix search with LIKE uses an ordinary index only in special conditions (because LIKE ignores case); GLOB 'An*' can use one more easily. You'll see why in Reading Query Plans.
  • For real "search the text" features (words, stemming, ranking), use a full-text search engine instead: SQLite's FTS5, PostgreSQL's tsvector, MySQL's FULLTEXT indexes, or a dedicated search service. LIKE '%word%' doesn't understand words, plurals or typos.

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. Which product names match name LIKE '%Set'?

    The % is only on the left, so anything may come before "Set", but nothing may follow it. SQLite's LIKE ignores ASCII case, so "...set" matches too.
  2. 2. What does 'Ivo' LIKE '__' return?

    _ stands for exactly one character. Use '___' for three characters or '__%' for at least two.
  3. 3. A table has emails '[email protected]', '[email protected]' and NULL. How many rows does WHERE email NOT LIKE '%@x.com' return?

    Only '[email protected]'. NULL NOT LIKE ... is unknown, so WHERE drops the NULL row. Add OR email IS NULL to keep it.
  4. 4. You need codes that contain a literal underscore. Which condition works in SQLite?

    '%_%' matches any non-empty text. SQLite has no default escape character, so you name one with ESCAPE; #_ then means a real underscore.
  5. 5. Which SQLite condition is true for "Portable Speaker"?

    GLOB is case-sensitive, * matches any text and ? exactly one character, so '*Speak?r' matches. The others fail on case or on the anchored start.

Summary

  • LIKE compares text against a pattern: % matches any sequence of characters (even none), _ matches exactly one.
  • Use 'abc%' for "starts with", '%abc' for "ends with" and '%abc%' for "contains".
  • NOT LIKE and LIKE both return unknown for NULL, so NULL rows are never matched; add IS NULL when you want them.
  • Case sensitivity varies: SQLite ignores case for ASCII letters only, PostgreSQL is case-sensitive (use ILIKE), MySQL and SQL Server follow the collation.
  • ESCAPE '!' lets !% and !_ match literal wildcard characters; PostgreSQL and MySQL use backslash by default.
  • SQLite's GLOB is case-sensitive and supports [...] character sets; other databases offer regular expressions (~, REGEXP, REGEXP_LIKE).
  • Leading wildcards prevent index use; use full-text search for real text search.

Next: Working with NULL, where you'll learn exactly how SQL handles missing values.