Like Operator

Learning Focus

Use this lesson to understand Like Operator with practical syntax and examples.

Concept Overview

Definition:

The LIKE operator in MySQL is used in WHERE clauses to search for a specified pattern in a column.

Why It's Important:

In real-world applications, exact matches aren't always feasible. Users may need to find values starting with, ending with, or containing certain substrings. The LIKE operator enables partial matching, making it essential for search functionality.

Broader Context:

The LIKE operator belongs to the set of conditional filtering tools in SQL and is a subset of pattern-matching functions. It's used primarily in SELECT, UPDATE, and DELETE queries when pattern-based retrieval or manipulation is required.

---

Basic Syntax & Rules

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Pattern Matching Characters:

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

Examples:

• 'A%' -> starts with "A"
• '%A' -> ends with "A"
• '%A%' -> contains "A"
• 'A_B' -> starts with "A", has any one character, then "B"

Key Considerations:

• Case-insensitive in most default MySQL configurations (collation-based).
• Patterns are string-based; avoid using LIKE with numeric types.
• % is greedy and can slow performance on large datasets if placed at the beginning of a pattern.

---

Step-by-Step Examples

Example 1: Basic Partial Match

Table: employees

id	name	department
1	Alice Johnson	Sales
2	Bob Andrews	Marketing
3	Alicia Keys	Sales

Query:

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

Explanation:

• Finds names starting with "Ali"

Result:

+----------------+
| name           |
+----------------+
| Alice Johnson  |
| Alicia Keys    |
+----------------+

---

Example 2: Find Entries Containing a Substring

Query:

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

Result:

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

---

Example 3: Single Character Wildcard

Query:

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

Result:

+-------------+
| name        |
+-------------+
| Alicia Keys |
+-------------+

---

Example 4: Use with NOT LIKE

Query:

SELECT name FROM employees
WHERE name NOT LIKE '%son%';

Result:

+-------------+
| name        |
+-------------+
| Bob Andrews |
| Alicia Keys |
+-------------+

---

Practical Use Cases

1. Customer Search Functionality

• Problem: A user searches for a customer named "Jon".

• Solution:

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

2. Product Filter by Category Suffix

• Use Case: Products ending with "Phone".

  SELECT * FROM products WHERE product_name LIKE '%Phone';
  

3. Email Domain Filter

• Use Case: All users with Gmail accounts.

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

4. Audit File Type by Extension

• Use Case: Logs ending in .log

  SELECT * FROM logs WHERE filename LIKE '%.log';
  

5. Filtering SKUs

• Use Case: Items with SKU starting with "PRD-"

  SELECT * FROM inventory WHERE sku LIKE 'PRD-%';
  

---

Common Mistakes & Troubleshooting

1. Using LIKE on Numeric Columns

• Wrong:

  SELECT * FROM orders WHERE id LIKE '123%';
  

• [OK] Solution: Use CAST(id AS CHAR) or use equality (=) if exact match needed.

2. Misplaced Wildcards

• % at the start slows down performance:

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

3. Forgetting Case Sensitivity (if changed collation)

• MySQL default: case-insensitive for most collations (utf8_general_ci)

• Use BINARY to enforce case-sensitive match:

  SELECT * FROM users WHERE BINARY username LIKE 'Admin%';
  

4. Overusing %

• Overgeneralized matches lead to large result sets and slower queries.

---

Best Practices

• Prefer indexed columns with LIKE 'abc%' (trailing % only) for better performance.
• Avoid leading % unless necessary ('%abc' disables index usage).
• Use full-text search for advanced matching instead of LIKE on large text columns.
• Use ILIKE (if available) or proper collation for case-insensitive comparisons in other DBMS.

---

Hands-On Practice

Sample Data Setup

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO customers (name, email) VALUES
('Alice Martin', 'alice@gmail.com'),
('Bob Marley', 'bob@yahoo.com'),
('Alicia Stone', 'stone.alicia@gmail.com'),
('George Smith', 'gsmith@hotmail.com');

---

Exercise 1: Find all customers whose name starts with "Ali"

Expected Query:

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

---

Exercise 2: Find customers using Gmail

Expected Query:

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

---

Exercise 3: Find customers whose name ends with "Smith"

Expected Query:

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

---

Connection to Other Concepts

Related Concept	Description
WHERE clause	LIKE is used inside the WHERE filter
IN, =, BETWEEN	Other conditional operators
REGEXP	More powerful pattern matching
JOIN	Combine with filtering across tables
Full-text search	Alternative to LIKE for large text fields

Prerequisite Knowledge:

• Basic SELECT statements
• Column filtering with WHERE

What to Learn Next:

• REGEXP for advanced matching
• Indexing strategies
• Full-text search
• Performance tuning for filtering

---

Visual Learning Diagram (Mermaid)

flowchart TD
  A[Basic SELECT Queries] --> B[Filtering with WHERE]
  B --> C[LIKE Operator]
  B --> D[=, IN, BETWEEN]
  C --> E[Pattern Matching with % and _]
  C --> F[NOT LIKE]
  C --> G[Case Sensitivity with BINARY]
  F --> H[Advanced Pattern Matching with REGEXP]
  G --> I[Indexing & Performance Considerations]
  I --> J[Full-Text Search]
  C --> K[Practical Use Cases]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef currentTopic stroke-width:4px

  class A,B,D,E,F,G,H,I,J,K allNodes
  class C allNodes,currentTopic

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: AND, OR, NOT Operators - Review the previous lesson to reinforce context.
• Next: Wildcards ('%' and '_') - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.