When you filter data with WHERE, exact comparisons such as = are not always enough. You often need to find rows where a text column starts with, ends with, or contains a certain fragment. For example, you may want every customer whose email ends with @gmail.com, or every book whose title begins with “The”.

SQL provides the LIKE operator together with wildcard characters to solve this problem. In this tutorial, you will learn how LIKE works, what the % and _ wildcards mean, how to escape special characters, and how different databases handle case sensitivity.

Prerequisites

To follow along, you need:

• A working database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
• Basic knowledge of the SELECT and WHERE clauses.
• A client such as psql, MySQL Workbench, DBeaver, or the IntelliJ IDEA Database tool window.

If you are new to filtering rows, review the earlier tutorial How Do I Filter SQL Query Results Using WHERE? first.

Sample Database

We will continue with the online bookstore domain used earlier in this series. The examples use a single customers table so that pattern matching remains the focus.

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(150),
    city          VARCHAR(100),
    phone_number  VARCHAR(20)
);

INSERT INTO customers (customer_id, customer_name, email, city, phone_number) VALUES
    (1, 'Alice Johnson',   '[email protected]',       'London',    '+44-20-7946-0001'),
    (2, 'Bob Smith',       '[email protected]',   'Liverpool', '+44-20-7946-0002'),
    (3, 'Charlie Brown',   '[email protected]',     'Leeds',     '+44-20-7946-0003'),
    (4, 'Diana Prince',    '[email protected]',     'Manchester','+44-20-7946-0004'),
    (5, 'Ethan Hunt',      '[email protected]',     'Bristol',   NULL),
    (6, 'Fiona O''Neill',  '[email protected]',   'Dublin',    '+353-1-555-0006'),
    (7, 'George Miller',   NULL,                    'London',    '+44-20-7946-0007');

Basic Syntax

The general form of a pattern-matching query is:

SELECT column_list
FROM table_name
WHERE column_name LIKE 'pattern';

LIKE compares a text value to a pattern that may contain wildcard characters:

Wildcard	Meaning
%	Matches zero or more characters of any kind.
_	Matches exactly one character of any kind.

You can also use NOT LIKE to invert the match.

Practical Example

Suppose we want every customer whose email is hosted on Gmail:

SELECT
    customer_id,
    customer_name,
    email
FROM customers
WHERE email LIKE '%@gmail.com';

How to read this query:

1. FROM customers supplies the rows.
2. WHERE email LIKE '%@gmail.com' keeps only rows whose email ends with @gmail.com. The % matches any characters (including none) that appear before @gmail.com.
3. The SELECT list returns three columns for each matching row.

Expected Result

customer_id	customer_name	email
1	Alice Johnson	[email protected]
3	Charlie Brown	[email protected]
5	Ethan Hunt	[email protected]

Note that George Miller does not appear because his email is NULL. LIKE never matches NULL.

Additional Examples

Starts With

Find every customer whose name starts with the letter A:

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'A%';

Expected result:

customer_id	customer_name
1	Alice Johnson

Ends With

Find every customer living in a city ending with pool:

SELECT customer_id, customer_name, city
FROM customers
WHERE city LIKE '%pool';

Expected result:

customer_id	customer_name	city
2	Bob Smith	Liverpool

Contains

Find every customer whose name contains on:

SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE '%on%';

Expected result:

customer_id	customer_name
1	Alice Johnson
4	Diana Prince
6	Fiona O’Neill

Fixed-Length Match with _

The _ wildcard matches exactly one character. To find every UK phone number where the area code is three digits after +44- and starts with 20-79, and the next two digits can be anything:

SELECT customer_name, phone_number
FROM customers
WHERE phone_number LIKE '+44-20-79__-____';

Expected result:

customer_name	phone_number
Alice Johnson	+44-20-7946-0001
Bob Smith	+44-20-7946-0002
Charlie Brown	+44-20-7946-0003
Diana Prince	+44-20-7946-0004
Ethan Hunt is skipped because his phone_number is NULL.

Negation with NOT LIKE

Find customers whose email is not a Gmail address (and is known):

SELECT customer_id, customer_name, email
FROM customers
WHERE email NOT LIKE '%@gmail.com'
  AND email IS NOT NULL;

Expected result:

customer_id	customer_name	email
2	Bob Smith	[email protected]
4	Diana Prince	[email protected]
6	Fiona O’Neill	[email protected]

The extra IS NOT NULL is required because NOT LIKE still returns UNKNOWN for NULL values, and rows with UNKNOWN conditions are excluded from the result.

Escaping Wildcard Characters

What if the text you are searching for actually contains a % or _ character? Use the ESCAPE clause to define an escape character.

For example, to find emails that literally contain an underscore:

SELECT customer_id, email
FROM customers
WHERE email LIKE '%\_%' ESCAPE '\';

Expected result:

customer_id	email
5	[email protected]

Any character can serve as the escape character; \ is a common choice.

Common Mistakes

Using = Instead of LIKE

Incorrect:

SELECT *
FROM customers
WHERE email = '%@gmail.com';

This looks for a literal string %@gmail.com and returns no rows. Wildcards work only with LIKE, not with =.

Correct:

SELECT *
FROM customers
WHERE email LIKE '%@gmail.com';

Forgetting That LIKE Ignores NULL

A condition such as email LIKE '%' does not match rows where email IS NULL. If you need those rows too, add an explicit IS NULL check:

SELECT *
FROM customers
WHERE email LIKE '%'
   OR email IS NULL;

Assuming LIKE Is Always Case-Insensitive

Case sensitivity depends on the database and column collation. LIKE 'a%' may or may not match Alice. See the compatibility notes below.

Leading Wildcards and Performance

A pattern such as LIKE '%gmail.com' prevents most databases from using a standard B-tree index on the column, because the search does not start from the beginning of the string. On small learning datasets this is fine, but for large tables it can be slow. Consider full-text search or specialized indexes when this becomes a problem, and always inspect the execution plan with EXPLAIN before drawing conclusions.

Database Compatibility

The LIKE operator and the % and _ wildcards are part of the SQL standard and are supported by every major database. However, some behaviors differ.

PostgreSQL

• LIKE is case-sensitive.
• Use ILIKE for a case-insensitive match:

SELECT *
FROM customers
WHERE customer_name ILIKE 'a%';

MySQL / MariaDB

• LIKE is case-insensitive by default because most character collations end in _ci (case-insensitive).
• Use LIKE BINARY to force case-sensitive comparison:

SELECT *
FROM customers
WHERE customer_name LIKE BINARY 'a%';

SQL Server

• Case sensitivity depends on the column or database collation.
• Supports additional bracket wildcards such as [abc], [^abc], and character ranges like [a-c]:

SELECT *
FROM customers
WHERE customer_name LIKE '[A-C]%';

These bracket expressions are not portable to other databases.

Oracle Database

• LIKE is case-sensitive.
• Combine with UPPER or LOWER for case-insensitive matches:

SELECT *
FROM customers
WHERE UPPER(customer_name) LIKE 'A%';

SQLite

• LIKE is case-insensitive for ASCII characters by default.
• Use GLOB for case-sensitive, Unix-style wildcard matching (* and ?).

When you need advanced pattern matching that goes beyond LIKE, most databases also support regular expressions through operators or functions such as ~ (PostgreSQL), REGEXP (MySQL, MariaDB, SQLite), and REGEXP_LIKE (Oracle). Consult your database documentation before relying on them.

Best Practices

• Use LIKE only when you actually need pattern matching. For exact matches, use = because it is simpler and can use indexes efficiently.
• Anchor patterns when possible. LIKE 'gmail%' is usually faster than LIKE '%gmail%' because the leading part is fixed.
• Always add IS NULL handling when your logic must include or exclude unknown values.
• Escape user input properly. When using LIKE from application code, use parameterized queries and escape the % and _ characters inside the input:

String sql = """
        SELECT customer_id, customer_name, email
        FROM customers
        WHERE email LIKE ? ESCAPE '\\'
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    String safeInput = userInput
            .replace("\\", "\\\\")
            .replace("%",  "\\%")
            .replace("_",  "\\_");

    statement.setString(1, "%" + safeInput + "%");

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long   customerId   = resultSet.getLong("customer_id");
            String customerName = resultSet.getString("customer_name");
            String email        = resultSet.getString("email");
        }
    }
}

This prevents both SQL injection and accidental wildcard behavior caused by raw user input.

• Document the intended case sensitivity of a query, especially when the same code runs against multiple database systems.

Conclusion

You learned how to search for text patterns in SQL using the LIKE operator with the % and _ wildcards, how to use NOT LIKE, how to escape special characters with ESCAPE, and how case sensitivity differs across database systems. Remember that LIKE never matches NULL, and that patterns starting with % may slow down queries on large tables.