👉What are MySQL Regular Expressions?
MySQL REGEXP Tutorial: MySQL Regular Expressions (REGEXP) provide advanced pattern-matching capabilities beyond simple wildcards. REGEXP allows for complex search criteria using metacharacters, making it a powerful tool for filtering and retrieving data efficiently.
Basic REGEXP Syntax
MySQL REGEXP Tutorial: The standard syntax for using REGEXP in a query is:
sql
SELECT * FROM table_name WHERE column_name REGEXP ‘pattern’;
- REGEXP (or RLIKE) is the operator used for pattern matching.
- ‘pattern’ defines the search criteria.
Example:
MySQL REGEXP Tutorial: Find all movies with the word “code” in the title:
sql
SELECT * FROM movies WHERE title REGEXP ‘code’;
This retrieves records regardless of the position of “code” in the title.
👉Advanced REGEXP Patterns
1. Matching Specific Characters
Find movies that start with ‘A’, ‘B’, ‘C’, or ‘D’:
sql
SELECT * FROM movies WHERE title REGEXP ‘^[ABCD]’;
2. Excluding Specific Characters
Find movies NOT starting with ‘A’, ‘B’, ‘C’, or ‘D’:
sql
SELECT * FROM movies WHERE title REGEXP ‘^[^ABCD]’;
👉MySQL REGEXP Metacharacters
| Symbol | Description | Example |
| * | Matches 0 or more instances of a string | ‘da*’ → matches “Da Vinci Code” |
| + | Matches 1 or more instances of a string | ‘mon+’ → matches “Angels and Demons” |
| ? | Matches 0 or 1 instance of a string | ‘com?’ → matches “comedy” |
| . | Matches any single character | ‘200.’ → matches 2005, 2008, etc. |
| [abc] | Matches any enclosed character | ‘[vwxyz]’ → matches X-Men |
| [^abc] | Excludes enclosed characters | ‘^[^vwxyz]’ → excludes movies starting with ‘v’, ‘w’, etc. |
| [0-9] | Matches any digit | ‘[0-9]’ → matches phone numbers |
| ^ | Matches pattern at the start | ‘^[cd]’ → matches titles starting with C or D |
| ` | ` | Defines alternatives |
👉Escaping Special Characters
MySQL REGEXP Tutorial: To match special characters like %, _, or ?, use an escape sequence:
sql
SELECT * FROM movies WHERE title REGEXP ’67\\%’;
👉Summary
- MySQL REGEXP enables advanced pattern matching for database searches.
- Metacharacters enhance flexibility, allowing for detailed search queries.
- Escape sequences handle special characters within patterns.
Use REGEXP to refine your SQL queries and unlock powerful search capabilities in MySQL!
