What are MySQL Wildcards?
MySQL Wildcards: MySQL wildcards are special characters used for pattern-based searches in a database. They work with the LIKE and NOT LIKE operators to find records that match complex criteria.
Why Use Wildcards?
Instead of writing long WHERE conditions, wildcards help simplify queries and make them more flexible.
Types of MySQL Wildcards
1️⃣ Percentage (%) Wildcard
- Matches zero or more characters in a string.
- Commonly used to search partial matches in a column.
Example 1: Find all addresses ending with “TX”
sql
SELECT * FROM members WHERE postal_address LIKE ‘%TX’;
Matches:
- Austin, TX
- Dallas, TX
- Houston, TX
2️⃣ Underscore (_) Wildcard
- Matches exactly one character in a string.
- Useful when searching for specific patterns with unknown characters.
Example 2: Find movies released between 2000-2009 (200x format)
sql
MySQL Wildcards: SELECT * FROM movies WHERE year_released LIKE ‘200_’;
Matches:
- 2001
- 2008
- 2005
Does NOT match: 1999, 2010
3️⃣ Using % at Different Positions
MySQL Wildcards: Wildcards can be used at the beginning, end, or both sides of a search term.
Example 3: Find movies with “code” anywhere in the title
sql
SELECT * FROM movies WHERE title LIKE ‘%code%’;
Matches:
- Code Name Black
- Da Vinci Code
Example 4: Find movies that start with “Code”
sql
SELECT * FROM movies WHERE title LIKE ‘Code%’;
Matches:
- Code Name Black
Does NOT match: Da Vinci Code
Example 5: Find movies that end with “code”
sql
SELECT * FROM movies WHERE title LIKE ‘%code’;
Matches:
- Da Vinci Code
Does NOT match: Code Name Black
4️⃣ NOT LIKE Operator (Exclude Matches)
MySQL Wildcards: To exclude results that match a wildcard pattern, use NOT LIKE.
Example 6: Find movies NOT released between 2000-2009
sql
SELECT * FROM movies WHERE year_released NOT LIKE ‘200_’;
Matches:
- 1995
- 2011
5️⃣ ESCAPE Keyword (For Special Characters)
If you need to search for wildcards as actual characters (not as wildcard functions), use ESCAPE.
Example 7: Find movie titles containing “67%”
sql
SELECT * FROM movies WHERE title LIKE ’67#%%’ ESCAPE ‘#’;
Matches:
- 67% Guilty
Key Takeaways
✔ LIKE and NOT LIKE help in pattern-based searching.
✔ % matches zero or more characters.
✔ _ matches exactly one character.
✔ ESCAPE allows searching for special characters like % or _.
✔ Wildcards make search queries more flexible and efficient.
Master MySQL wildcards to improve database searches and queries!
Meta Description: Learn how to use MySQL Wildcards with LIKE, NOT LIKE, %, _, and ESCAPE. Improve search queries with pattern matching and SQL filtering.
