What Are Regular Expressions in MySQL?
MySQL REGEXP Tutorial: Regular Expressions (REGEXP) are powerful pattern-matching tools used to filter and retrieve data that meets specific conditions. Unlike wildcards (LIKE, NOT LIKE), REGEXP allows more complex and flexible search queries.
Basic Syntax for REGEXP in MySQL
MySQL REGEXP Tutorial: The syntax for using regular expressions in MySQL is:
sql
SELECT * FROM table_name WHERE column_name REGEXP ‘pattern’;
- SELECT – Fetches data from the database.
- WHERE column_name – The column on which the search is applied.
- REGEXP ‘pattern’ – Defines the search pattern.
- RLIKE – A synonym for REGEXP.
Example Queries Using REGEXP
1️⃣MySQL REGEXP Tutorial: Find movies that contain “code” in the title:
sql
SELECT * FROM movies WHERE title REGEXP ‘code’;
This query returns all movie titles that contain “code” anywhere in the text.
2️⃣MySQL REGEXP Tutorial: Find movies that start with A, B, C, or D:
sql
SELECT * FROM movies WHERE title REGEXP ‘^[abcd]’;
The ^ symbol ensures that the search starts at the beginning of the title, and [abcd] specifies the allowed starting letters.
3️⃣ Find movies that do NOT start with A, B, C, or D:
sql
SELECT * FROM movies WHERE title REGEXP ‘^[^abcd]’;
The [^abcd] pattern excludes any titles starting with A, B, C, or D.
Common REGEXP Metacharacters & Usage
| Character | Description | Example Query | Usage |
| * | Matches 0 or more characters | SELECT * FROM movies WHERE title REGEXP ‘da*’; | Finds “Da Vinci Code”, “Daddy’s Little Girls” |
| + | Matches 1 or more characters | SELECT * FROM movies WHERE title REGEXP ‘mon+’; | Finds “Angels and Demons” |
| ? | Matches 0 or 1 occurrence | SELECT * FROM categories WHERE category_name REGEXP ‘com?’; | Finds “comedy”, “romantic comedy” |
| . | Matches any single character | SELECT * FROM movies WHERE year_released REGEXP ‘200.’; | Finds movies released in “2000-2009” |
| [abc] | Matches any of the listed characters | SELECT * FROM movies WHERE title REGEXP ‘[vwxyz]’; | Finds “X-Men”, “Da Vinci Code” |
| [^abc] | Matches characters NOT in the list | SELECT * FROM movies WHERE title REGEXP ‘^[^vwxyz]’; | Excludes titles starting with V, W, X, Y, or Z |
| [0-9] | Matches any digit (0-9) | SELECT * FROM members WHERE contact_number REGEXP ‘[0-9]’; | Finds members with a contact number |
| ^ | Matches the start of a string | SELECT * FROM movies WHERE title REGEXP ‘^[cd]’; | Finds “Code Name Black”, “Daddy’s Little Girls” |
| ` | ` | Defines alternatives (OR condition) | `SELECT * FROM movies WHERE title REGEXP ‘^[cd] |
| [[:<:]] | Matches word boundaries (beginning) | SELECT * FROM movies WHERE title REGEXP ‘[[:<:]]for’; | Finds titles starting with “For” |
| [[:>:]] | Matches word boundaries (end) | SELECT * FROM movies WHERE title REGEXP ‘ack[[:>:]]’; | Finds titles ending with “ack” |
Escape Characters in MySQL REGEXP
MySQL REGEXP Tutorial: The backslash (\) is used as an escape character to search for symbols like %, _, or . that have special meanings.
✅ Example: Searching for “67%” in movie titles:
sql
SELECT * FROM movies WHERE title LIKE ’67#%%’ ESCAPE ‘#’;
Here, the # symbol is used to escape the % so MySQL treats it as a literal character rather than a wildcard.
Key Takeaways
✔MySQL REGEXP Tutorial: MySQL REGEXP is more powerful than LIKE for complex pattern matching.
✔ Supports various metacharacters to refine search results.
✔ Case-insensitive by default.
✔ Use escape characters to search for special symbols.
Ready to improve your MySQL queries? Start using REGEXP for efficient and powerful database searches!
