MySQL Wildcards Tutorial: LIKE, NOT LIKE, %, _, and ESCAPE Best 2025

MySQL Wildcards Tutorial: MySQL wildcards are powerful tools that allow for flexible and efficient pattern matching in database queries. They are commonly used with the LIKE and NOT LIKE operators to filter data based on partial matches.

This guide covers different wildcard types, their usage, and real-world examples to help you understand how to improve search queries in MySQL.

---

👉Table of Contents

1. MySQL Wildcards Tutorial: What Are MySQL Wildcards?
2. Why Use Wildcards in SQL?
3. Types of MySQL Wildcards
   • % (Percentage Wildcard)
   • _ (Underscore Wildcard)
   • NOT LIKE Operator
   • ESCAPE Keyword
4. Using Wildcards in SQL Queries
5. Real-World Examples
6. Summary

---

1. What Are MySQL Wildcards?

MySQL Wildcards Tutorial: MySQL wildcards are special characters used in SQL queries to search for patterns in text data. They are mainly used with the LIKE and NOT LIKE operators in the WHERE clause.

✅ Use Case: When you don’t know the exact value but need to search for a partial match.

Example: Searching for all movies with the word “code” in the title:

sql

SELECT * FROM movies WHERE title LIKE ‘%code%’;

---

2. Why Use Wildcards in SQL?

MySQL Wildcards Tutorial: If you’re already using SQL SELECT and WHERE clauses, you might wonder why wildcards are necessary.

Consider this complex query:

sql

SELECT * FROM members WHERE postal_address = ‘Austin, TX’ 

OR postal_address = ‘Dallas, TX’ 

OR postal_address = ‘Iola, TX’ 

OR postal_address = ‘Houston, TX’;

Instead, using wildcards makes it much simpler:

sql

SELECT * FROM members WHERE postal_address LIKE ‘%TX’;

✅ Wildcards simplify queries and make them more efficient in searching large datasets.

---

3. Types of MySQL Wildcards

MySQL Wildcards Tutorial: MySQL supports two primary wildcards:

1. % (Percentage Wildcard)

• Matches zero or more characters.
• Used when you don’t know how many characters will be in a search pattern.

Syntax:

sql

SELECT * FROM table_name WHERE column_name LIKE ‘pattern%’;

Example:
Find all movie titles that contain the word “code”:

sql

SELECT * FROM movies WHERE title LIKE ‘%code%’;

✅ Matches “Code Name Black”, “Da Vinci Code”, etc.

---

2. _ (Underscore Wildcard)

• MySQL Wildcards Tutorial: Matches exactly one character in a specific position.
• Useful when you know the length of the word but not the exact characters.

Example:
Find all movies released in the 2000s (2000-2009):

sql

SELECT * FROM movies WHERE year_released LIKE ‘200_’;

✅ Matches 2000, 2001, …, 2009, but not 2010.

---

3. NOT LIKE Operator

MySQL Wildcards Tutorial: Used to exclude results that match a certain pattern.

Example:
Find movies that were not released in the 2000s:

sql

SELECT * FROM movies WHERE year_released NOT LIKE ‘200_’;

✅ Returns movies from 2010, 2011, 2012, etc.

---

4. ESCAPE Keyword

MySQL Wildcards Tutorial: Used when you need to search for special characters (like % or _) as actual text rather than as wildcards.

Example:
Find a movie title containing “67%” as text:

sql

SELECT * FROM movies WHERE title LIKE ’67#%%’ ESCAPE ‘#’;

✅ The # tells SQL to treat the % as a normal character instead of a wildcard.

---

4. Using Wildcards in SQL Queries

1. Match a Word at the Beginning of a String

Find movie titles that start with “Code”:

sql

SELECT * FROM movies WHERE title LIKE ‘Code%’;

✅ Matches: “Code Name Black”
❌ Does NOT match: “Da Vinci Code”

---

2. Match a Word at the End of a String

Find movie titles that end with “Code”:

sql

SELECT * FROM movies WHERE title LIKE ‘%Code’;

✅ Matches: “Da Vinci Code”
❌ Does NOT match: “Code Name Black”

---

3. Match Words in the Middle of a String

Find all movies containing “code” anywhere:

sql

SELECT * FROM movies WHERE title LIKE ‘%code%’;

✅ Matches: “Code Name Black”, “Da Vinci Code”

---

4. Find Strings with a Fixed Character Length

Find movies from the year 200x (one unknown digit at the end):

sql

SELECT * FROM movies WHERE year_released LIKE ‘200_’;

✅ Matches: 2000, 2001, …, 2009

---

5. Exclude Specific Patterns

Find movies not released in 200x:

sql

SELECT * FROM movies WHERE year_released NOT LIKE ‘200_’;

✅ Returns movies from 2010, 2011, 2012, etc.

---

5. Real-World Examples of Wildcards

1. Find Customers from a Specific City

sql

SELECT * FROM customers WHERE city LIKE ‘New%’;

✅ Matches: “New York”, “New Orleans”

---

2. Find All Email Addresses from Gmail

sql

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

✅ Matches: “john@gmail.com”, “sara@gmail.com”

---

3. Find Product Codes Starting with “AB”

sql

SELECT * FROM products WHERE product_code LIKE ‘AB%’;

✅ Matches: “AB123”, “AB567”

---

6. Summary

✅ LIKE and Wildcards help search for partial matches in SQL queries.
✅ % (Percentage Wildcard): Matches any number of characters.
✅ _ (Underscore Wildcard): Matches exactly one character.
✅ NOT LIKE Operator: Filters out results matching a pattern.
✅ ESCAPE Keyword: Searches for special characters as actual text.

Mastering wildcards can help optimize search queries in large databases, making your SQL searches more powerful and efficient!

SQL GROUP BY and HAVING CLAUSE

Download My SQL