Wildcards ('%' and '_')

Learning Focus

Use this lesson to understand Wildcards ('%' and '_') with practical syntax and examples.

Concept Overview

Wildcards are special characters you use with pattern matching operators (most commonly LIKE and ILIKE) to match text when you do not know the exact value.

The two wildcard characters are:

• % - matches zero or more characters
• _ - matches exactly one character

Why is it important?

Wildcards show up in everyday tasks:

• search and filtering in admin tools (ILIKE '%term%')
• detecting inconsistent formats (filenames, emails, SKUs)
• implementing prefix-based lookups (LIKE 'ORD-%') that can stay fast with indexes

They also have performance implications: patterns that start with % often cannot use a normal B-tree index.

Where does it fit?

Wildcards are part of filtering and conditions. You use them inside WHERE and often combine them with boolean logic (AND / OR / NOT). When wildcard patterns are not expressive enough, PostgreSQL also supports regular expressions (~, ~*) and SIMILAR TO.

---

Syntax & Rules

Core Syntax

-- Case-sensitive
SELECT *
FROM t
WHERE text_col LIKE 'pattern';

-- Case-insensitive
SELECT *
FROM t
WHERE text_col ILIKE 'pattern';

-- Negation
SELECT *
FROM t
WHERE text_col NOT LIKE 'pattern';

Available Options / Parameters

Token	Meaning	Example
%	zero or more characters	email ILIKE '%@gmail.com'
_	exactly one character	sku LIKE 'AB_5%'
ESCAPE	defines an escape character for literal %/_	LIKE '%\_%' ESCAPE '\\'

Key Rules and Considerations

• % can match an empty string. 'A%' matches 'A'.
• _ matches exactly one character. 'A_' matches 'AB' but not 'A'.
• In PostgreSQL, LIKE is case-sensitive; use ILIKE when you want case-insensitive matching.
• If you need to match a literal _ or %, escape it and use ESCAPE.
• LIKE 'prefix%' can often use an index; LIKE '%contains%' usually cannot.

---

Step-by-Step Examples

Example 1: Starts With (Prefix Match) (Beginner)

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL
);

INSERT INTO employees (full_name, department) VALUES
  ('Alice Johnson', 'Engineering'),
  ('Alicia Keys', 'Sales'),
  ('Bob Smith', 'Marketing'),
  ('Carla Doe', 'Engineering');

-- Names starting with "Ali"
SELECT employee_id, full_name
FROM employees
WHERE full_name LIKE 'Ali%'
ORDER BY employee_id;

Expected output:

 employee_id | full_name
-------------+---------------
 1           | Alice Johnson
 2           | Alicia Keys
(2 rows)

Explanation:

• 'Ali%' matches any value that begins with Ali.

---

Example 2: Ends With (Suffix Match) and Contains (Intermediate)

CREATE TABLE files (
  file_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  filename text NOT NULL
);

INSERT INTO files (filename) VALUES
  ('error.log'),
  ('access.log'),
  ('report_2026_03.csv'),
  ('report_2026_04.csv'),
  ('README.md');

-- Ends with ".log"
SELECT file_id, filename
FROM files
WHERE filename LIKE '%.log'
ORDER BY file_id;

Expected output (suffix):

 file_id | filename
---------+-----------
 1       | error.log
 2       | access.log
(2 rows)

Now a contains match:

SELECT file_id, filename
FROM files
WHERE filename LIKE '%report%'
ORDER BY file_id;

Expected output (contains):

 file_id | filename
---------+-----------------
 3       | report_2026_03.csv
 4       | report_2026_04.csv
(2 rows)

Explanation:

• LIKE '%.log' is a suffix pattern.
• LIKE '%report%' starts with % and is typically not index-friendly on large tables.

---

Example 3: Exactly-One-Character Wildcard _ (Intermediate)

CREATE TABLE inventory (
  item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku     text NOT NULL,
  price   numeric(10,2) NOT NULL CHECK (price >= 0)
);

INSERT INTO inventory (sku, price) VALUES
  ('AB15-RED', 19.99),
  ('AB25-BLU', 21.50),
  ('ABX5-GRN', 18.00),
  ('AB35-BLK', 23.00);

-- "AB" + any single character + "5" + anything
SELECT item_id, sku
FROM inventory
WHERE sku LIKE 'AB_5%'
ORDER BY item_id;

Expected output:

 item_id | sku
---------+---------
 1       | AB15-RED
 3       | ABX5-GRN
(2 rows)

Explanation:

• _ matches exactly one character, so it matches 1 and X.

---

Example 4: Escaping Literal % and _ (Advanced)

CREATE TABLE notes (
  note_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body    text NOT NULL
);

INSERT INTO notes (body) VALUES
  ('Progress: 100%'),
  ('Progress: 100 percent'),
  ('Token: user_id'),
  ('Token: userXid');

-- Match a literal percent sign '%'
SELECT note_id, body
FROM notes
WHERE body LIKE '%\%%' ESCAPE '\\'
ORDER BY note_id;

-- Match a literal underscore '_' inside "user_id"
SELECT note_id, body
FROM notes
WHERE body LIKE '%user\_id%' ESCAPE '\\'
ORDER BY note_id;

Expected output (literal '%'):

 note_id | body
---------+---------------
 1       | Progress: 100%
(1 row)

Expected output (literal '_'):

 note_id | body
---------+--------------
 3       | Token: user_id
(1 row)

Explanation:

• Without escaping, _ would match any single character and also match userXid.

---

Practical Use Cases

1) Email domain filtering (case-insensitive)

SELECT user_id, email
FROM users
WHERE email ILIKE '%@gmail.com'
ORDER BY user_id;

2) File extension filtering

SELECT file_id, filename
FROM files
WHERE filename LIKE '%.csv'
ORDER BY file_id;

3) SKU format checks

SELECT item_id, sku
FROM inventory
WHERE sku LIKE 'AB_5%';

4) Data cleanup targeting

UPDATE leads
SET phone = replace(phone, '-', '')
WHERE phone LIKE '%-%'
RETURNING lead_id, phone;

5) Admin prefix search (index-friendly pattern)

SELECT order_id, external_id
FROM orders
WHERE external_id LIKE 'ORD-%'
ORDER BY order_id DESC
LIMIT 50;

---

Common Mistakes & Troubleshooting

1) Forgetting that _ is a wildcard

Wrong SQL:

SELECT *
FROM notes
WHERE body LIKE '%user_id%';

Bad outcome:

• Matches userXid because _ matches any single character.

Fix:

SELECT *
FROM notes
WHERE body LIKE '%user\_id%' ESCAPE '\\';

---

2) Using LIKE when you need case-insensitive matching

Wrong SQL:

SELECT email
FROM users
WHERE email LIKE '%@Company.com';

Bad outcome:

• Misses values with different casing.

Fix:

SELECT email
FROM users
WHERE email ILIKE '%@company.com';

---

3) Leading % causes slow scans

Wrong approach:

SELECT *
FROM users
WHERE email ILIKE '%son%';

Bad outcome:

• Often forces a scan on large tables.

Fix options:

-- Prefer a prefix match when possible
SELECT * FROM users WHERE email ILIKE 'son%';

-- For substring search at scale, consider pg_trgm indexing
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);

---

4) Not escaping user input

Wrong approach:

• Building a pattern from user input without escaping % and _.

Bad outcome:

• Wildcard injection (matches more rows than intended).

Fix:

• Escape % and _ before building the pattern, then use ESCAPE.

---

Debugging checklist:

1. Decide whether the match is prefix, suffix, contains, or fixed-position.
2. Verify exactly where % and _ appear in the pattern.
3. Choose LIKE vs ILIKE intentionally.
4. If matching literal %/_, add ESCAPE.
5. If performance is slow, check for leading % and run EXPLAIN.

---

Best Practices

• ✅ Anchor patterns when possible ('prefix%', '%.csv'). ❌ Avoid leading % on large tables unless you have specialized indexing.
• ✅ Use ILIKE for user-facing search when case should be ignored. ❌ Avoid missing matches due to inconsistent casing.
• ✅ Escape % and _ in user input. ❌ Avoid treating raw user input as a safe pattern.
• ✅ Use _ only when you mean “exactly one character”. ❌ Avoid _ when you meant an underscore literal.
• ✅ Use search-specific tools (trigram/full-text) when substring search is core. ❌ Avoid scaling LIKE '%term%' as a long-term search strategy.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_books (
  book_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title   text NOT NULL
);

INSERT INTO practice_books (title) VALUES
  ('Learn SQL Basics'),
  ('MySQL Mastery'),
  ('Advanced SQL Patterns'),
  ('SQL for Dummies'),
  ('Secrets of SQL');

Exercise 1 (Easy): Titles that start with "SQL"

Task: Return book titles that start with SQL.

-- Your SQL here

Solution:

SELECT title
FROM practice_books
WHERE title LIKE 'SQL%'
ORDER BY book_id;

---

Exercise 2 (Medium): Titles containing "SQL" anywhere

Task: Return book titles that contain SQL anywhere in the string.

-- Your SQL here

Solution:

SELECT title
FROM practice_books
WHERE title LIKE '%SQL%'
ORDER BY book_id;

---

Exercise 3 (Advanced): Match a literal underscore

Task: Create a table with a value containing _ and query for the literal underscore using ESCAPE.

-- Your SQL here

Solution:

CREATE TABLE practice_tokens (token text NOT NULL);
INSERT INTO practice_tokens (token) VALUES ('user_id'), ('userid');

SELECT token
FROM practice_tokens
WHERE token LIKE '%\_%' ESCAPE '\\'
ORDER BY token;

---

Connection to Other Concepts

Concept	Why it matters
Like Operator	wildcards are primarily used with LIKE / ILIKE
AND / OR / NOT	combine pattern predicates with other conditions
Regular expressions	more powerful matching when wildcards are not enough
Indexes	determine whether prefix patterns can be fast
ESCAPE	required to match literal wildcard characters

---

Visual Learning Diagram

flowchart TD
  A[WHERE Filtering] --> B[LIKE / ILIKE]
  B --> C[% wildcard]
  B --> D[_ wildcard]
  B --> E[ESCAPE]
  B --> F[Performance]
  F --> G[Prefix: 'abc%']
  F --> H[Contains: '%abc%']
  H --> I[pg_trgm / Full-Text]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class C highlight
  class D highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using _ when you meant a literal underscore	unexpected matches	escape _ and add ESCAPE
Leading % on large tables	slow queries	prefer prefix patterns or specialized indexing
Not choosing LIKE vs ILIKE intentionally	missed matches	decide case behavior up front
Treating user input as a pattern	wildcard injection	escape % and _
Overusing wildcards for validation	brittle rules	consider CHECK constraints or regex where appropriate

---

Quick Reference

col LIKE 'A%';
col LIKE '%A';
col LIKE 'A_B';
col ILIKE '%@company.com';
col LIKE '%\_%' ESCAPE '\\';

What's Next

• Previous: Like Operator - Review the previous lesson to reinforce context.
• Next: IN Operator - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.