LIKE compares text with a pattern in which percent matches zero or more characters and underscore matches exactly one character.
Use LIKE when you know part of a value rather than its exact spelling. A customer search for names starting with A is a pattern question. Understanding the two wildcard symbols also explains why a search containing a literal percent sign needs special handling.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How LIKE and wildcard patterns works
The pattern 'A%' matches a value that begins with A, including the single-letter value A because percent can match zero characters. '%a' matches a value ending in a, and '%ar%' matches a value containing ar anywhere. The underscore represents one character, so 'B_n' fits Ben but would not fit a two-character or four-character name. LIKE compares the entire value to the pattern; without percent signs, a partial fragment does not automatically become a contains search.
Case sensitivity is not a universal property of LIKE. It depends on the engine and its collation or configuration. MySQL frequently uses case-insensitive collations; PostgreSQL LIKE is case-sensitive and offers the separate ILIKE extension; SQLite’s default LIKE usually folds ASCII case but not all Unicode case; SQL Server follows the relevant collation. A search interface should have an explicit case and accent policy instead of relying on whichever default an installation happened to choose.
Literal wildcards and the ESCAPE clause
Percent and underscore may also be ordinary user characters. ESCAPE specifies a character that marks a wildcard as literal inside the pattern, for example LIKE '%!%%' ESCAPE '!' to search for text containing a percent. An application that promises a literal contains search should escape the escape character itself and both wildcard characters before adding its surrounding percent signs. Bind the finished pattern as a parameter rather than constructing SQL by joining user text into the statement.
NULL text does not match any LIKE pattern, including '%', because the comparison is unknown. The empty string does match '%' and does not match '_'. Pattern length and Unicode character behaviour deserve testing with real names. A leading wildcard can make a conventional index less useful, because the search has no fixed starting prefix. For large searchable documents, investigate the database’s full-text search rather than assuming a contains pattern is an equivalent search engine. Choose prefix matching when the user’s task allows it.
MySQL, PostgreSQL, SQL Server and SQLite
Percent and underscore are the portable wildcards. SQL Server also supports bracket pattern syntax, which is not portable. For literal wildcard handling, use an explicit ESCAPE clause and verify your client’s string escaping. PostgreSQL ILIKE is an extension; LOWER(name) LIKE LOWER(pattern) is an alternative whose Unicode and indexing behaviour still depends on the engine.
Check the engine-specific syntax and version requirements in PostgreSQL pattern matching.
Practice task and worked solution
Return the customer whose name fits B, then one character, then n. Use the single-character wildcard rather than a percent wildcard.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT id, name FROM customers
WHERE name LIKE 'B_n' ORDER BY id;id | name
2 | BenB and n are literal letters; the underscore fills the position occupied by e. The pattern has exactly three character positions. Bn would be too short, while a longer spelling such as Been would not satisfy this three-position pattern.
Compare name LIKE 'B_n' with name LIKE 'B%' to see an exact-length pattern versus a prefix. Test SELECT '50%' LIKE '%!%%' ESCAPE '!' to confirm literal-percent matching without adding new rows. Also compare an empty string with '%' and '_' so zero-length and one-character requirements become observable. Check non-ASCII case examples in your chosen engine separately.
SQL LIKE and Wildcards: Percent, Underscore and Escaping Syntax
LIKE and wildcard patterns
SELECT id, name FROM customers
WHERE name LIKE 'A%'
ORDER BY id;SQL LIKE and Wildcards: Percent, Underscore and Escaping Summary
LIKEcompares text with a pattern:%matches any number of characters, including none, and_matches exactly one.'A%'means starts with A,'%a'ends with a, and'%ar%'contains ar anywhere.- To search for a literal
%or_, mark it with an escape character declared byESCAPE. - Binding a search term as a parameter prevents injection but doesn't stop
%and_acting as wildcards. - Case sensitivity of
LIKEdiffers by database, and a leading%usually prevents ordinary index use.
SQL LIKE and Wildcards: Percent, Underscore and Escaping Examples
Each example shows the code, the output it produces and what each part does.
Names that start with A
The pattern starts with the literal A and allows any remaining characters. Ana matches it. Ben, Cara and Dev do not begin with A, so the example has one result regardless of the order the rows were inserted.
SELECT id, name FROM customers
WHERE name LIKE 'A%'
ORDER BY id;id | name
1 | AnaCommon SQL LIKE and Wildcards: Percent, Underscore and Escaping Mistakes
A pattern with no wildcard
WHERE name LIKE 'ar' looks for the complete two-character value ar. It does not find Cara. Use '%ar%' for a contains search. Also, an unescaped user underscore acts as a wildcard even in a parameterised pattern: parameter binding prevents SQL injection but does not change LIKE’s pattern meaning.
SQL LIKE and Wildcards: Percent, Underscore and Escaping in Real Projects
Search-as-you-type customer lookup
A customer lookup can start with prefix matching to keep results focused as a user types. Document whether the search is case-sensitive and whether wildcard characters are literal. Apply a sensible limit and a stable order so the interface remains predictable when several names share the prefix.
A search input needs both a binding policy and a pattern policy. Binding protects SQL syntax, while wildcard escaping controls what a user’s percent or underscore means. Keep those responsibilities separate. If a search promises literal fragments, escape wildcards deliberately; if it offers advanced patterns, explain that behaviour in the interface.
SQL LIKE and Wildcards: Percent, Underscore and Escaping FAQ
Does percent require at least one character?
No. % matches zero or more characters, so 'A%' also matches the one-letter value A. The underscore is different: _ requires exactly one character, so 'B_n' matches Ben but not Bn.
Is LIKE the same as a regular expression?
No. LIKE has a small wildcard language of just % and _. Regular expression features, such as character classes and repetition counts, use other operators or functions that differ by engine: ~ in PostgreSQL, REGEXP in MySQL, and an optional REGEXP function in SQLite.
Does a bound parameter make wildcards literal?
No. Binding separates data from SQL syntax, which prevents injection, but LIKE still interprets wildcard characters inside the parameter value. If users should be able to search for a literal 50%, escape %, _ and the escape character itself in the search text before binding it.
Is LIKE case-sensitive?
It depends on the database. PostgreSQL's LIKE is case-sensitive and ILIKE ignores case. SQLite's LIKE ignores case for ASCII letters by default, and MySQL follows the column's collation, which is usually case-insensitive. LOWER(name) LIKE LOWER(?) behaves the same everywhere.
Can LIKE use an index?
A pattern with a fixed start, such as 'A%', can often use an ordinary index on the column, although collation settings matter in some databases. A pattern starting with % can't, so it scans every row. For searching inside large text columns, use full-text search or, in PostgreSQL, a trigram index.
SQL LIKE and Wildcards: Percent, Underscore and Escaping Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which customer matches the pattern 'B_n'?
SQL LIKE and Wildcards: Percent, Underscore and Escaping Quiz
2 questions to check what you learned.
What does _ mean in a LIKE pattern?
- Exactly one character
- Zero or more characters
- One literal underscore in every pattern
Which pattern finds a literal fragment anywhere in text?
- Only a trailing percent, which restricts the match to a prefix
- Percent before and after the properly escaped fragment
- No percent signs, which compares the whole value with the fragment