SQLFiltering Databeginner40 min read

Pattern Matching with LIKE

Search text using SQL pattern matching with LIKE and PostgreSQL ILIKE, master percent and underscore wildcards, escape literal special characters, and understand indexing and search performance.

In this lesson, you will learn

✓Explain how LIKE compares text against a pattern
✓Use percent (%) to match zero or more characters
✓Use underscore (_) to match exactly one character
✓Combine wildcards to build more specific patterns
✓Perform case-insensitive searches using PostgreSQL ILIKE
✓Escape literal percent signs and underscores using ESCAPE
✓Understand how NULL affects pattern matching
✓Recognize when LIKE searches can benefit from indexes
✓Choose appropriate approaches for prefix, substring, and full-text searches

1. What Is Pattern Matching?

Sometimes we do not know the complete value stored in a text column. We may know only the beginning of a name, a part of an email address, or a few characters in a product code.

Exact equality with = requires the compared text to match the value. LIKE lets us describe a pattern instead.

For example, if we want customer names beginning with 'Sam', we can use LIKE 'Sam%'. The percent sign represents the remaining characters, if any.

LIKE is a pattern-matching operator, not a general-purpose search engine. It matches characters according to a pattern, rather than understanding synonyms, word meanings, or search relevance.

Exact Matching Versus Pattern Matching

sql

1
2
3
4
5
6
7
8
9
-- Exact match: only this complete name
SELECT customer_id, full_name
FROM customers
WHERE full_name = 'Sam';
 
-- Pattern match: names beginning with Sam
SELECT customer_id, full_name
FROM customers
WHERE full_name LIKE 'Sam%';

The first query looks for the exact value Sam. The second can match Sam, Samantha, Samuel, and other names beginning with Sam. Actual results depend on the data.

2. The Two Main Wildcards

SQL LIKE patterns support two primary wildcard characters.

The percent sign (%) matches zero or more characters. It can represent no characters at all, one character, or a long sequence.

The underscore (_) matches exactly one character at its position. It does not match zero characters or multiple characters by itself.

All other characters are normally treated as literal characters unless they are part of a special pattern construct supported by the database.

SQL LIKE Wildcards

Pattern	Meaning	Possible match	Does not match
'J%'	J followed by zero or more characters	'J', 'John', 'Jane'	'Alex'
'%sql%'	Contains sql anywhere	'PostgreSQL', 'nosql'	'Python'
'%@gmail.com'	Ends with @gmail.com	'a@gmail.com'	'a@yahoo.com'
'H_t'	H, one character, then t	'Hat', 'Hit', 'Hot'	'Heat'
'A__'	A followed by exactly two characters	'App', 'Ant'	'Apple'
'A__%'	A followed by at least two characters	'App', 'Apple'	'A'
'%cat'	Ends with cat	'cat', 'wildcat'	'catalog'
'cat%'	Begins with cat	'cat', 'catalog'	'wildcat'

3. Understanding the Percent (%) Wildcard

The percent sign is useful when the length of the matching text is unknown. It can appear at the beginning, end, or middle of a pattern.

'Jo%' means the text starts with Jo. '%son' means the text ends with son. '%sql%' means the text contains sql somewhere.

Because % can match zero characters, a pattern such as 'Jo%' can match the value Jo itself.

Find Text by Beginning, Ending, and Contents

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Starts with 'Jo'
SELECT full_name
FROM customers
WHERE full_name LIKE 'Jo%';
 
-- Ends with 'son'
SELECT full_name
FROM customers
WHERE full_name LIKE '%son';
 
-- Contains 'sql'
SELECT title
FROM articles
WHERE title LIKE '%sql%';

The first pattern checks the beginning, the second checks the ending, and the third allows characters on both sides of the searched text.

4. Understanding the Underscore (_) Wildcard

Underscore represents exactly one character. It is useful when a particular position can vary but the overall pattern length is constrained.

For example, 'H_t' matches Hat, Hit, and Hot because each has one character between H and t. It does not match Heat because there are two characters between H and t.

Use multiple underscores when multiple character positions may vary.

Match a Fixed Number of Characters

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Three-character values beginning with A
SELECT code
FROM products
WHERE code LIKE 'A__';
 
-- Values with exactly five characters
SELECT code
FROM products
WHERE code LIKE '_____';
 
-- Values beginning with A and having at least three characters
SELECT code
FROM products
WHERE code LIKE 'A__%';

A__ requires A plus exactly two characters. Five underscores require exactly five characters. A__% requires A plus at least two more characters, followed by any additional characters.

5. Practical Searches with LIKE

Partial matching is useful for searching customer names, email domains, telephone prefixes, product codes, and article titles.

The following examples assume that the relevant columns exist and contain text values. Telephone numbers are often best stored as text because they may contain leading zeros, country codes, plus signs, and formatting characters.

Search Names, Emails, and Product Codes

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Find names beginning with Ali
SELECT customer_id, full_name
FROM customers
WHERE full_name LIKE 'Ali%';
 
-- Find email addresses ending with @gmail.com
SELECT customer_id, full_name, email
FROM customers
WHERE email LIKE '%@gmail.com';
 
-- Find product codes beginning with ELEC-
SELECT product_id, sku
FROM products
WHERE sku LIKE 'ELEC-%';
 
-- Find phone numbers beginning with the literal text (415)
SELECT customer_id, phone
FROM customers
WHERE phone LIKE '(415)%';

Each query checks a different part of the text. The last example assumes phone numbers are stored with the literal formatting shown. If the data uses a different format, the pattern must be adjusted.

6. Case Sensitivity: LIKE and ILIKE

In PostgreSQL, LIKE performs case-sensitive pattern matching for ordinary text comparisons. For example, LIKE 'python%' does not ordinarily match Python Basics because the uppercase P differs from lowercase p.

PostgreSQL provides ILIKE for case-insensitive pattern matching. It uses the database's locale and case-handling rules, so behavior for some non-ASCII characters can depend on the environment.

ILIKE is a PostgreSQL extension, not part of the SQL standard. Other database systems may use different operators or collations for case-insensitive comparisons.

Compare LIKE and ILIKE

sql

1
2
3
4
5
6
7
8
9
-- Case-sensitive matching
SELECT article_id, title
FROM articles
WHERE title LIKE '%python%';
 
-- Case-insensitive matching in PostgreSQL
SELECT article_id, title
FROM articles
WHERE title ILIKE '%python%';

The ILIKE query can match Python, PYTHON, or python within the title. The LIKE query requires the matching characters to use the same case as the pattern under ordinary PostgreSQL text matching.

7. Alternative Case-Insensitive Matching

If you need to write SQL that works across different database systems, LOWER can be used to normalize text before comparison. This is a common alternative when ILIKE is unavailable.

However, wrapping a column in a function may prevent an ordinary index on the original column from supporting the condition efficiently. In PostgreSQL, an expression index can sometimes help.

Use LOWER for Case-Insensitive Matching

sql

1
2
3
4
-- Normalize the text and the pattern to lowercase
SELECT article_id, title
FROM articles
WHERE LOWER(title) LIKE LOWER('%Python%');

Both sides are converted to lowercase before matching. This approach can be more portable than ILIKE, but exact behavior for international text depends on the database's case-conversion rules.

8. Escaping Literal Percent Signs and Underscores

Percent and underscore are special characters in LIKE patterns. Sometimes you need to search for those characters themselves.

For example, a product description may contain '20% off', or a username may contain an underscore. If you write the character directly into a pattern, SQL interprets it as a wildcard.

The ESCAPE clause defines a character that marks the next pattern character as literal when appropriate. In the following example, ! is the escape character.

Search for a Literal Percent Sign

sql

1
2
3
4
5
6
7
8
9
-- Find descriptions containing the literal text '20%'
SELECT promo_id, description
FROM discounts
WHERE description LIKE '%20!%%' ESCAPE '!';
 
-- Find values containing a literal underscore
SELECT username
FROM users
WHERE username LIKE '%!_%' ESCAPE '!';

Output

-- Example result for the first query promo_id | description ---------+------------------------------ 4 | Autumn Sale: 20% off monitors -- Example result for the second query username ---------------- user_v2 alex_admin

In the first pattern, !% represents a literal percent sign, while the other % symbols remain wildcards. In the second pattern, !_ represents a literal underscore, and the surrounding percent signs allow any text before or after it.

9. Escape Characters and User-Supplied Search Text

When users enter search text into an application, they may expect percent and underscore to be ordinary characters rather than wildcards. If the application inserts the text directly into a LIKE pattern, those characters can change the meaning of the search.

For example, searching for the user input 50% without escaping it may match text beginning with 50 followed by anything. If the intended search is for the literal text 50%, the wildcard must be escaped.

Applications should use parameterized queries to keep values separate from SQL syntax. Parameterization prevents SQL injection through values, but it does not automatically make LIKE wildcard characters literal. If literal matching is required, escape the pattern characters as well.

Understand the Difference Between Wildcards and Literal Text

sql

1
2
3
4
5
6
7
8
9
-- Wildcard search: text beginning with 50
SELECT description
FROM discounts
WHERE description LIKE '50%';
 
-- Literal percent sign: text containing '50%'
SELECT description
FROM discounts
WHERE description LIKE '%50!%%' ESCAPE '!';

The first query uses % as a wildcard. The second searches for a literal percent sign after 50. In application code, use parameterized SQL and correctly escape the user's search text when literal matching is required.

10. NULL and LIKE

LIKE and ILIKE require text to compare against a pattern. If the text expression is NULL, the result is normally UNKNOWN.

A WHERE clause keeps only rows where the condition is TRUE. Therefore, rows with NULL text values do not pass an ordinary LIKE filter.

If your requirement includes missing values, write that requirement explicitly using IS NULL or another suitable condition.

Find Matching and Missing Email Addresses

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Find email addresses ending in @gmail.com
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@gmail.com';
 
-- Find customers with no email address
SELECT customer_id, email
FROM customers
WHERE email IS NULL;
 
-- Find Gmail addresses or missing addresses
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@gmail.com'
   OR email IS NULL;

The first query excludes NULL email values because the pattern comparison evaluates to UNKNOWN. The final query explicitly includes customers whose email is missing.

11. Combining LIKE with AND and OR

LIKE can be combined with logical operators to create more useful filters. AND requires all conditions to be satisfied, while OR allows alternative patterns.

When mixing AND and OR, group the alternatives with parentheses so that the query reflects the intended business rule.

Find Active Articles Matching Either Pattern

sql

1
2
3
4
5
6
7
SELECT article_id, title, is_active
FROM articles
WHERE is_active = TRUE
  AND (
      title ILIKE '%sql%'
      OR title ILIKE '%database%'
  );

The article must be active, and its title must contain either sql or database. Parentheses ensure the active requirement applies to both search alternatives.

12. LIKE Pattern Boundaries

A common source of errors is misunderstanding where the pattern must match.

A pattern beginning with text and ending with % checks a prefix. A pattern beginning with % and ending with text checks a suffix. A pattern with wildcards on both sides checks for a substring.

The pattern must match the entire value, but % can absorb any unmatched prefix or suffix. This is why '%sql%' finds sql within a larger string.

How Pattern Placement Changes Results

Pattern	Requirement	Example match
'sql%'	Starts with sql	'sql basics'
'%sql'	Ends with sql	'learn sql'
'%sql%'	Contains sql anywhere	'learn sql today'
'_at'	Exactly three characters, ending in at	'cat'
'cat_'	Exactly four characters, starting with cat	'cats'
'cat%'	Starts with cat; any remaining length	'cat', 'catalog'

13. LIKE and Index Performance

Pattern matching performance depends on how much of the value is known and where the wildcard appears.

A prefix search such as LIKE 'ELEC-%' can often use a suitable B-tree index because the beginning of the value is known. Whether PostgreSQL can use that index depends on factors such as the operator class, collation, data type, and query plan.

A substring search such as LIKE '%bluetooth%' does not normally let an ordinary B-tree index efficiently locate the matching range, because the beginning of the text is unknown. The database may need to examine many rows.

Do not assume every leading-wildcard query must always perform a sequential scan. PostgreSQL may choose other available indexes or plans, and the actual plan depends on the schema, statistics, and data distribution.

Prefix Search Versus Substring Search

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Prefix search: a suitable B-tree index may help
SELECT product_id, sku
FROM products
WHERE sku LIKE 'ELEC-%';
 
-- Substring search: an ordinary B-tree index
-- generally cannot efficiently support this pattern
SELECT product_id, description
FROM products
WHERE description LIKE '%bluetooth%';
 
-- Inspect the query plan
EXPLAIN
SELECT product_id, sku
FROM products
WHERE sku LIKE 'ELEC-%';

EXPLAIN shows the planned query operations. Index usage is not guaranteed: PostgreSQL compares the available plans and chooses one based on its estimates.

14. PostgreSQL Trigram Indexes for Substring Search

If an application frequently searches for arbitrary substrings in large text columns, PostgreSQL's pg_trgm extension may help. It provides trigram-based operators and index support for LIKE, ILIKE, and other string comparisons.

Trigrams are overlapping groups of three characters. For example, a string such as 'database' contains character groups that can be used to narrow down candidate matches.

Trigram indexes are not free: they consume storage and add work to inserts, updates, and deletes. Very short search patterns may also be less selective. Test performance with representative data before adding an index.

Create a Trigram Index

sql

1
2
3
4
5
6
7
8
9
10
11
12
-- Run once if the extension is not already installed
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- Add an index for substring searches
CREATE INDEX idx_products_description_trgm
ON products
USING GIN (description gin_trgm_ops);
 
-- Search for a substring
SELECT product_id, description
FROM products
WHERE description ILIKE '%bluetooth%';

The GIN trigram index can support suitable substring searches. The extension must be available and permitted in the PostgreSQL environment. Index use still depends on the query and planner estimates.

15. LIKE Versus Full-Text Search

LIKE is appropriate when you need character-pattern matching. It does not automatically understand word roots, relevance, or linguistic meaning.

PostgreSQL full-text search is designed for searching tokenized documents. It can support word-oriented searches, stemming, and relevance-oriented workflows. Trigram indexing is often a better fit for arbitrary substring matching.

Choose the method that matches the user experience you want to build.

Choosing a Search Technique

Requirement	Possible approach	Why
SKU starts with ELEC-	LIKE 'ELEC-%'	Prefix matching
Description contains bluetooth	LIKE or ILIKE with pg_trgm	Substring matching
Search articles for related words	PostgreSQL full-text search	Word-oriented search features
Exact email address	= or an appropriate equality comparison	Exact matching
Case-insensitive prefix search	ILIKE with an appropriate index strategy	Case-insensitive pattern matching

16. Common Mistakes

These mistakes commonly cause incorrect results, unexpected case behavior, or slow searches.

17. Guided Practice

Try writing each query yourself before opening the solutions. Assume the required tables and columns exist.

18. Guided Practice Solutions

Compare your answers with the solutions. Pay attention to wildcard placement, case sensitivity, and NULL handling.

Practice Solutions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Task 1: names beginning with Ana
SELECT customer_id, full_name
FROM customers
WHERE full_name LIKE 'Ana%';
 
-- Task 2: emails ending with @example.com
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@example.com';
 
-- Task 3: product codes containing exactly four characters
SELECT product_id, sku
FROM products
WHERE sku LIKE '____';
 
-- Task 4: case-insensitive title search in PostgreSQL
SELECT article_id, title
FROM articles
WHERE title ILIKE '%sql%';
 
-- Task 5: descriptions containing the literal text 15%
SELECT promo_id, description
FROM discounts
WHERE description LIKE '%15!%%' ESCAPE '!';
 
-- Task 6: missing email or matching domain
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@example.com'
   OR email IS NULL;
 
-- Task 7: active articles matching either word
SELECT article_id, title
FROM articles
WHERE is_active = TRUE
  AND (
      title ILIKE '%database%'
      OR title ILIKE '%programming%'
  );

Four underscores match exactly four characters. ILIKE performs case-insensitive matching in PostgreSQL. The ESCAPE clause makes the percent sign literal. Task 6 explicitly includes missing email values.

19. Multiple-Choice Questions

Test your understanding of pattern matching. Each answer includes a short explanation.

20. SQL Interview Questions and Answers

These questions cover the concepts most likely to be tested in beginner SQL interviews.

21. Lesson Summary

LIKE lets you search text using patterns. The percent sign (%) matches zero or more characters, while the underscore (_) matches exactly one character. Pattern placement determines whether you are searching for a prefix, suffix, or substring.

In PostgreSQL, use ILIKE for convenient case-insensitive pattern matching. Use ESCAPE when you need literal percent signs or underscores. Remember that NULL comparisons normally evaluate to UNKNOWN, so missing values must be handled explicitly.

For performance, prefix searches may benefit from a suitable B-tree index. Arbitrary substring searches may benefit from PostgreSQL's pg_trgm extension. Use EXPLAIN and representative data to verify the actual plan instead of assuming every pattern will use an index.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Confusing % with _

Why it happens: Both characters are wildcards, but they match different amounts of text.

Incorrect Approach

sql

-- This requires exactly one character after A
SELECT sku
FROM products
WHERE sku LIKE 'A_';

Correct Solution

sql

-- This allows zero or more characters after A
SELECT sku
FROM products
WHERE sku LIKE 'A%';

Key takeaway: Use underscore when exactly one character must occupy a position. Use percent when any number of characters, including zero, is acceptable.

#2 Assuming LIKE is case-insensitive in PostgreSQL

Why it happens: Some applications perform case-insensitive matching by default, so developers may expect the same behavior from SQL.

Incorrect Approach

sql

SELECT title
FROM articles
WHERE title LIKE '%python%';

Correct Solution

sql

SELECT title
FROM articles
WHERE title ILIKE '%python%';

Key takeaway: ILIKE is PostgreSQL's convenient case-insensitive pattern-matching operator. Behavior for non-ASCII characters can depend on locale and case-handling rules.

#3 Searching for a literal wildcard without escaping it

Why it happens: The developer expects percent and underscore to be ordinary characters even though LIKE interprets them specially.

Incorrect Approach

sql

SELECT description
FROM discounts
WHERE description LIKE '%20%%';

Correct Solution

sql

SELECT description
FROM discounts
WHERE description LIKE '%20!%%' ESCAPE '!';

Key takeaway: The incorrect pattern uses the percent sign as a wildcard. The corrected pattern treats the percent sign immediately following ! as a literal character.

#4 Expecting an ordinary B-tree index to efficiently support every pattern

Why it happens: Developers assume that indexing a text column automatically makes every text search fast.

Incorrect Approach

sql

SELECT product_id, description
FROM products
WHERE description LIKE '%bluetooth%';

Correct Solution

sql

-- For repeated substring searches in PostgreSQL,
-- evaluate a suitable trigram index.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX idx_products_description_trgm
ON products
USING GIN (description gin_trgm_ops);

Key takeaway: A leading wildcard usually prevents efficient range lookup with a conventional B-tree index. A trigram index may help, but measure the query plan and performance before adding an index.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

LIKE matches text against a pattern rather than requiring exact equality.
Percent (%) matches zero or more characters.
Underscore (_) matches exactly one character.
A pattern without wildcards can behave like exact text matching.
In PostgreSQL, LIKE is case-sensitive for ordinary text matching, while ILIKE provides case-insensitive matching.
The ESCAPE clause lets you search for literal wildcard characters.
A comparison with NULL normally produces UNKNOWN, so LIKE does not match NULL text values.
A leading wildcard often prevents an ordinary B-tree index from efficiently supporting a pattern search.
Index usefulness depends on the pattern, operator class, collation, and database configuration.
For large-scale substring searches, PostgreSQL trigram indexes may be useful; full-text search is designed for word-oriented searches.

Was this lesson helpful?

Found an error? Report an issue