SQL LIKE Operator Explained: Wildcards, Case Sensitivity and Performance

A support agent needs every customer whose email ends in a certain company domain. A tester wants all order codes that start with a given prefix. These are jobs for the SQL LIKE operator, which matches text against a simple pattern inside a WHERE clause.

We ran every query in SQLite 3.50, and the results shown are from that run. LIKE and its two wildcards are standard SQL, so the basics work everywhere, but we point out where databases differ.

What does LIKE do?

column LIKE pattern is true when the text matches the pattern. The pattern is ordinary text plus two wildcards. Without any wildcards, LIKE behaves much like =, apart from case rules in some databases.

The sample table

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, code TEXT);
INSERT INTO products (name, code) VALUES
 ('Blue Pen','P-100'), ('blue pencil','P-101'), ('Notebook','N_200'),
 ('Pen Stand','P-102'), ('Open Box','B-300'), ('50% off coupon','C-1'),
 (NULL,'X-1'), ('Café Mug','M-1');

The % wildcard

SELECT name FROM products WHERE name LIKE 'Pen%';
-- Pen Stand

SELECT name FROM products WHERE name LIKE '%pen%';
-- Blue Pen, blue pencil, Pen Stand, Open Box

SELECT name FROM products WHERE name LIKE '%box';
-- Open Box

'Pen%' means “starts with Pen”, '%box' means “ends with box”, and '%pen%' means “contains pen anywhere”. Look closely at the contains result. Open Box matched, because “Open” contains the letters “pen”. Substring searches often return more than you expect, so check the results before using them to update or delete data.

The other rows matched regardless of capital letters, because SQLite’s LIKE ignores case for ASCII letters by default. More on that below.

The _ wildcard

SELECT code FROM products WHERE code LIKE 'P-10_';
-- P-100, P-101, P-102

Each underscore stands for exactly one character. 'P-10_' matches five-character codes that start with “P-10”, but not P-1000. Underscores are useful for fixed-format values such as product codes, postcodes and dates stored as text.

Escaping % and _

What if the text itself contains a percent sign or an underscore? Our Notebook code is N_200, and one product name contains “50%”. An unescaped pattern like '%%%' simply matched all 7 non-NULL names. To treat a wildcard as a literal character, choose an escape character with ESCAPE:

SELECT name FROM products WHERE name LIKE '%\%%' ESCAPE '\';
-- 50% off coupon

The pattern now reads as “anything, a real percent sign, anything”. The escape character can be any single character you choose. This matters when patterns are built from user input. If a user searches for 50% and you insert it unescaped, the percent sign acts as a wildcard and the search becomes “starts with 50”.

NOT LIKE and NULL

SELECT COALESCE(name, '<NULL>') FROM products WHERE name NOT LIKE '%pen%';
-- Notebook, 50% off coupon, Café Mug

The product with a NULL name is missing. We confirmed it with counts: the table has 8 rows, but name LIKE '%pen%' OR name NOT LIKE '%pen%' returned only 7. Comparing NULL with anything gives “unknown”, not true or false, so the row fails both conditions. If NULLs should be included, add OR name IS NULL. Our guide to SQL NULL values explains this three-valued logic.

Case sensitivity

This is where databases differ most. In SQLite, LIKE ignores case for plain ASCII letters but not for other characters:

SELECT name FROM products WHERE name LIKE 'CAFÉ%';   -- (no rows)
SELECT name FROM products WHERE name LIKE 'Café%';   -- Café Mug

Upper-case “É” did not match lower-case “é”. For case-sensitive matching, SQLite offers GLOB, which uses * and ? as wildcards, or the case_sensitive_like setting. Both returned only “blue pencil” and “Open Box” for a lower-case “pen” search.

The other databases in this table are described from their documentation; we only ran SQLite. The safe habit is to never assume. Check what your database does with one quick query before relying on it.

Performance: the leading wildcard problem

We filled a table with 200,000 email addresses, indexed the column and compared two searches:

WHERE email LIKE 'user1999%'
-- plan: SEARCH big USING COVERING INDEX ix_email (email>? AND email<?)
-- 0.06 ms per query, 111 rows

WHERE email LIKE '%1999@example.com'
-- plan: SCAN big
-- 25.04 ms per query, 20 rows

A prefix search can use the index, because the database can jump to “user1999” in sorted order, like finding a surname in a phone book. A pattern starting with % could match anywhere, so every row had to be checked. Here that was roughly 400 times slower, and the gap grows with the table.

One SQLite detail: the index was only used because the column’s collation matched LIKE’s case-insensitive behaviour, which we set with COLLATE NOCASE. Each database has its own rules here. PostgreSQL, for example, may need a special operator class for LIKE prefix searches. Our SQL indexes guide and query optimization guide cover reading plans like these.

Beyond LIKE

• Full-text search such as PostgreSQL tsvector, MySQL FULLTEXT or SQLite FTS5 handles word searches in large text quickly, with ranking.
• Trigram indexes, such as PostgreSQL’s pg_trgm extension, can speed up %contains% searches.
• Regular expressions are available as ~ in PostgreSQL and REGEXP in MySQL for complex patterns.
• A reversed column with an index can make “ends with” searches fast, at the cost of extra storage.
• A search engine such as Elasticsearch or OpenSearch suits fuzzy, typo-tolerant product search.

LIKE for testers

Testers use LIKE constantly to check data after a test run, for example finding records created by an automated test with WHERE email LIKE 'autotest+%'. Give test data a clear, unique prefix so such queries return only your records. When testing a search feature, include inputs containing %, _, accented characters and mixed case, since these are exactly the cases our examples show going wrong. Our list of SQL interview questions for testers includes more queries like these.

Using LIKE safely with parameters

Search boxes usually build the pattern from user input. Never paste the input into the SQL text. Pass it as a parameter and add the wildcards in SQL:

-- SQLite and PostgreSQL use || to join strings
SELECT name FROM products WHERE name LIKE '%' || ? || '%';

-- MySQL uses CONCAT
SELECT name FROM products WHERE name LIKE CONCAT('%', ?, '%');

Parameters stop SQL injection, but the wildcards inside the input still act as wildcards. We ran the SQLite version with two search terms:

"50%"    raw: ['50% off coupon', '500 sheets paper']
         escaped: ['50% off coupon']
"Blue_"  raw: ['Blue_Pen', 'BlueXPen']
         escaped: ['Blue_Pen']

Without escaping, “50%” also matched “500 sheets paper”, and “Blue_” matched “BlueXPen”. A tiny helper fixed both by escaping the escape character first, then % and _, before passing the term with ESCAPE '\':

def escape_like(s, esc="\\"):
    return s.replace(esc, esc * 2).replace("%", esc + "%").replace("_", esc + "_")

Many ORMs and query builders offer an equivalent option, so check your library’s documentation before writing your own.

Final thoughts

LIKE is the quickest way to search text in SQL. Remember the two wildcards, escape them when the data contains them, add IS NULL when NULLs matter, and verify case behaviour on your own database. For large tables, prefer prefix patterns, and move to full-text search when users need to find words anywhere.

Frequently asked questions

Keep learning