Wildcards ('%' and '_')

Learning Focus

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

Concept Overview

What Are Wildcards?

In MySQL, wildcards are special characters used with the LIKE operator in WHERE clauses to search for patterns within string data.

The two main wildcards are:

• %: Matches any sequence of characters (including no characters).
• _: Matches a single character.

Why Is This Important?

Wildcards are crucial for flexible and partial text searches, allowing users to:

• Retrieve data without knowing the exact value
• Implement pattern matching
• Improve user-driven filtering (e.g., search bars)

Where It Fits in the Bigger Picture

Wildcards are part of:

• Filtering mechanisms within SELECT, UPDATE, or DELETE
• Conditional querying
• Pattern-based data extraction

They belong to the broader SQL family of operators and are often used in conjunction with logical operators (AND, OR, NOT).

---

Basic Syntax & Rules

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

Wildcards:

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

Examples:

• 'A%' -> starts with "A"
• '%z' -> ends with "z"
• '_b%' -> second letter is "b"

Notes:

• Case sensitivity depends on collation (e.g., utf8_general_ci is case-insensitive).
• Use ESCAPE if you need to treat % or _ literally.

---

Step-by-Step Examples

Setup:

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Alice Johnson', 'Engineering'),
(2, 'Bob Smith', 'Marketing'),
(3, 'Carla Doe', 'Engineering'),
(4, 'David Li', 'HR'),
(5, 'Eliot Brown', 'Marketing');

---

Example 1: Match names starting with "A"

SELECT name FROM employees
WHERE name LIKE 'A%';

Output: Output:

+---------------+
| name          |
+---------------+
| Alice Johnson |
+---------------+

---

Example 2: Names ending with "n"

SELECT name FROM employees
WHERE name LIKE '%n';

Output: Output:

+---------------+
| name          |
+---------------+
| Alice Johnson |
| Eliot Brown   |
+---------------+

---

Example 3: Names with "o" as the third character

SELECT name FROM employees
WHERE name LIKE '__o%';

Output: Output:

+-----------+
| name      |
+-----------+
| Bob Smith |
+-----------+

---

Example 4: Filter departments that contain "ket"

SELECT department FROM employees
WHERE department LIKE '%ket%';

Output: Output:

+-------------+
| department  |
+-------------+
| Marketing   |
| Marketing   |
+-------------+

---

Practical Use Cases

1. Customer Name Search

SELECT * FROM customers
WHERE name LIKE 'John%';

Finds all customers whose names start with "John".

---

2. Email Validation

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

Filters users with Gmail addresses.

---

3. Product SKU Lookup

SELECT * FROM products
WHERE sku LIKE 'AB_5%';

Matches SKUs starting with "AB", any 1 character, then "5".

---

4. Job Title Match

SELECT * FROM jobs
WHERE title LIKE '%Manager';

Finds all jobs with titles ending in "Manager".

---

5. Phone Number Area Code

SELECT phone FROM contacts
WHERE phone LIKE '(212)%';

Matches numbers in the 212 area code.

---

Common Mistakes & Troubleshooting

Mistake	Explanation	Fix
Using = instead of LIKE	= needs exact match	Replace = with LIKE
Not escaping special chars	Searching % or _ literally fails	Use ESCAPE keyword
Forgetting wildcard	'A' vs 'A%' yields different results	Use % or _ as needed
Case-sensitivity confusion	Depends on collation	Use LOWER() if needed

Tip: Use LOWER() to make comparisons case-insensitive:

WHERE LOWER(name) LIKE 'a%'

---

Best Practices

• [OK] Use % only where necessary to avoid performance hits.
• [OK] Anchor patterns when possible (e.g., 'John%' is faster than '%ohn%').
• Avoid leading % in large tables; it disables index use.
• [OK] Combine LIKE with LIMIT or ORDER BY when paginating.
• [OK] For complex patterns, consider using REGEXP instead.

---

Hands-On Practice

Sample Data:

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

INSERT INTO books VALUES
(1, 'Learn SQL Basics'),
(2, 'MySQL Mastery'),
(3, 'Advanced SQL Patterns'),
(4, 'SQL for Dummies'),
(5, 'Secrets of SQL');

---

Exercise 1: Find books starting with "SQL"

-- Solution:
SELECT title FROM books
WHERE title LIKE 'SQL%';

[OK] Expected Output:

SQL for Dummies

---

Exercise 2: Titles containing "SQL" anywhere

-- Solution:
SELECT title FROM books
WHERE title LIKE '%SQL%';

[OK] Expected Output:

Learn SQL Basics
MySQL Mastery
Advanced SQL Patterns
SQL for Dummies
Secrets of SQL

---

Exercise 3: Titles where "SQL" is the last word

-- Solution:
SELECT title FROM books
WHERE title LIKE '% SQL';

[OK] Expected Output:

Secrets of SQL

---

Connection to Other Concepts

• Prerequisites:
  • SELECT, WHERE, FROM clauses
  • Basic string data types (e.g., VARCHAR)
• Related To:
  • LIKE operator
  • IN, BETWEEN, NOT LIKE
  • REGEXP, INSTR(), SUBSTRING()
• What to Learn Next:
  • Full-Text Search (MATCH ... AGAINST)
  • REGEXP and advanced pattern matching
  • Performance tuning for large LIKE queries

---

Visual Learning Diagram

flowchart TD
  A[Basic SELECT & WHERE] --> B[Filtering with LIKE]
  B --> C{Wildcard Matching}
  C --> D[% : Any Characters]
  C --> E[_ : Single Character]
  D --> F[Pattern Search: 'A%']
  E --> G[Pattern Search: '__b%']
  F --> H(Wildcards in WHERE)
  G --> H
  H --> I[Real-world Queries]
  H --> J[Use in SELECT/UPDATE/DELETE]
  I --> K((Advanced Filtering))
  J --> K
  K --> L[Next: REGEXP]
  K --> M[Next: Full-Text Search]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I,J,K,L,M allNodes
  class H stroke-width:4px
  class D,E rect
  class C diamond
  class K,L,M circle

---

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SELECT * FROM users WHERE name LIKE "A%";
SELECT * FROM users WHERE email LIKE "%@example.com";
SELECT * FROM users WHERE code LIKE "_B%";

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.