MySQL Regex: Complete Guide from Basics to Performance

1. Introduction

Using Regular Expressions in MySQL

Benefits of Using Regular Expressions in MySQL

• Advanced searching capabilities: Easily extract data containing specific strings or patterns.
• Check data integrity: Useful for validating input data (e.g., email address format checks).
• More powerful than the LIKE operator: Allows flexible definition of complex search conditions compared to LIKE with wildcards (% or _).

Purpose and Structure of This Article

1. Fundamentals and Syntax of MySQL Regular Expressions
2. Regular Expression Functions Added in MySQL 8.0 and Later
3. Practical Use Cases (Validation, Data Extraction, Format Conversion)
4. Considerations When Using Regular Expressions (Performance, Security)
5. FAQ (Frequently Asked Questions)
6. Conclusion

2. Basics and Syntax of Regular Expressions in MySQL

How to Use Regular Expressions in MySQL

SELECT * FROM table_name WHERE column_name REGEXP 'regex';

SELECT * FROM table_name WHERE column_name RLIKE 'regex';

SELECT * FROM users WHERE name REGEXP 'mountain';

Basic Regular Expression Patterns

Differences from the LIKE Operator

SELECT * FROM users WHERE name LIKE '%mountain%';

• With LIKE, you can search for data containing “mountain”, but only simple wildcards (% and _) can be used.

SELECT * FROM users WHERE name REGEXP '^mountain';

• Using REGEXP allows you to extract only data with names that start with “mountain”.

How to Specify Multiple Patterns

SELECT * FROM users WHERE name REGEXP 'Sato|Tanaka';

Negative Searches

SELECT * FROM users WHERE name REGEXP '^[^mountain]';

Summary

• In MySQL, using REGEXP enables more powerful pattern searching than the regular LIKE.
• By understanding the basic syntax ( ., ^, $, [], *, +, etc.), flexible searches become possible.
• Understanding the differences from LIKE and using them appropriately is important.
• Using | enables searching for multiple patterns.
• By leveraging the negative pattern [ ], you can search for data that does not contain specific characters.

3. Regular Expression Functions Added in MySQL 8.0 and Later

3.1 REGEXP_LIKE()

Overview

Syntax

REGEXP_LIKE(column_name, 'regex_pattern' [, flags])

• column_name: the column to search
• 'regex_pattern': the regular expression to use as the search condition
• flags (optional): set case‑sensitivity (specify i for case‑insensitive matching)

Example

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail.com|@yahoo.co.jp)$');

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

3.2 REGEXP_INSTR()

Overview

Syntax

REGEXP_INSTR(column_name, 'regex_pattern' [, start_position, occurrence, return_option, flags])

• start_position (optional): where to begin the search (default is 1)
• occurrence (optional): which match to return (default is 1)
• return_option (optional): 0 (return start position) or 1 (return position after the matched substring)
• flags (optional): specify i for case‑insensitive matching

Example

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• The result is the position where @ is found (e.g., 5 for user@example.com).

3.3 REGEXP_SUBSTR()

Overview

Syntax

REGEXP_SUBSTR(column_name, 'regex_pattern' [, start_position, occurrence, flags])

• occurrence (optional): which match to return (default is 1)
• flags (optional): case‑sensitivity (ignore with i)

Example

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

SELECT message, REGEXP_SUBSTR(message, '[0-1-9]+') AS first_number FROM logs;

3.4 REGEXP_REPLACE()

Overview

Syntax

REGEXP_REPLACE(column_name, 'regex_pattern', 'replacement' [, occurrence, flags])

• replacement: the string to substitute for the matched portion
• occurrence (optional): replace only the nth match (default replaces all matches)
• flags (optional): specify i for case‑insensitive matching

Example

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

3.5 Summary

4. Practical Applications of MySQL Regular Expressions

4.1 Email Address Validation

Overview

SQL Used

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$';

Explanation

• ^[a-zA-Z0-9._%+-]+ → One or more alphanumeric characters, dots, underscores, or plus signs before the @
• @[a-zA-Z0-9.-]+ → Domain name after the @
• .[a-zA-Z]{2,}$ → Ends with a top-level domain (TLD) of at least two characters (e.g., .com, .jp, .net, etc.)

4.2 Japanese Phone Number Format Check

Overview

SQL Used

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

Explanation

• 0[789]0-[0-9]{4}-[0-9]{4} → Mobile numbers (e.g., 090-xxxx-xxxx, 080-xxxx-xxxx, etc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → Landline numbers (e.g., 03-xxxx-xxxx, 06-xxxx-xxxx, etc.)

4.3 Credit Card Number Format Check

Overview

SQL Used

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

Explanation

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa (16 or 13 digits)
• ^5[1-5][0-9]{14}$ → Mastercard (16 digits)
• ^3[47][0-9]{13}$ → American Express (15 digits)

4.4 HTML Tag Removal

Overview

SQL Used

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

Explanation

• '<[^>]+>' → Removes all HTML tags between < and >

4.5 Postal Code Format Check (Japan)

Overview

SQL Used

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

Explanation

• ^[0-9]{3}-[0-9]{4}$ → Postal codes with a “3-digit‑4-digit” format

4.6 Username Filtering (Prohibited Word Detection)

Overview

SQL Used

Summary

• Email address, phone number, and credit card number validation can be performed using regular expressions.
• Removing HTML tags and converting data formats can also be applied.
• Filtering prohibited words and checking postal codes, etc., are effective for improving database security and consistency.

5. Precautions When Using Regular Expressions (Performance & Security)

5.1 Regular Expression Performance Optimization

Measures to Improve Performance

1. Combine LIKE and FULLTEXT indexes

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

2. Use simple regular expressions

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';

3. Filter target data beforehand

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';

5.2 Security Risks and Countermeasures

1. Regular Expression DoS Attacks (ReDoS)

• Avoid unlimited matches such as .*.
• Use ^ and $ to limit the search range.
• Do not pass user input directly to REGEXP.

2. SQL Injection and Regular Expressions

SELECT * FROM users WHERE username REGEXP '$input';

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 Compatibility Across Versions

Summary

• REGEXP does not use indexes, so performance optimization is important.
• To prevent ReDoS (regular expression DoS attacks), avoid excessive use of wildcards.
• To prevent SQL injection, do not apply user input directly to REGEXP.

6. FAQ (Frequently Asked Questions)

6.1 What regular expression patterns can be used in MySQL?

6.2 What is the difference between LIKE and REGEXP?

6.3 What are the differences in regular expressions between MySQL 5.x and MySQL 8.x?

6.4 How to troubleshoot when regular expressions do not work as expected?

1. Is the escaping done correctly?

   SELECT * FROM users WHERE email REGEXP '.com$';

2. Try the i flag with REGEXP_LIKE()

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

3. Check the data encoding

   SHOW VARIABLES LIKE 'character_set_database';

6.5 How to improve the performance of REGEXP functions?

1. Pre-filter using LIKE

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';

2. Leverage indexes

   ALTER TABLE users ADD FULLTEXT(email);

Summary

• REGEXP is more powerful than LIKE, but you need to be careful about performance.
• In MySQL 8.0, new regular expression functions (such as REGEXP_LIKE()) have been added, enabling more flexible processing.
• Improving performance relies on combining with LIKE, leveraging indexes, and designing simple regular expressions.

7. Summary

7.1 Review of Key Points

1. Basics of MySQL Regular Expressions

• REGEXP operator enables more flexible pattern matching than the standard LIKE.
• Common regular expression patterns
• ^ (start), $ (end), . (any single character), [] (character class), + (one or more repetitions), etc.

2. New Regular Expression Functions in MySQL 8.0 and Later

• REGEXP_LIKE(): alternative to the REGEXP operator
• REGEXP_INSTR(): returns the start position of a matching string
• REGEXP_SUBSTR(): extracts the matching string
• REGEXP_REPLACE(): replaces strings using regular expressions

3. Practical Use Cases

• Email address validation
• Phone number and credit card number checks
• Removing HTML tags
• Postal code format verification
• Detecting prohibited words
• Data cleansing (removing whitespace, commas, etc.)

4. Cautions When Using Regular Expressions

• Performance optimization
• REGEXP does not use indexes and results in a full table scan, so be careful.
• You can improve this by combining LIKE or FULLTEXT indexes.
• Security measures
• To prevent ReDoS (regular expression DoS attacks), avoid excessive use of .*.
• Use placeholder to prevent SQL injection.

5. FAQ (Frequently Asked Questions)

• Patterns usable with MySQL regular expressions
• Differences between LIKE and REGEXP
• Feature differences between MySQL 5.x and 8.x
• Troubleshooting when regular expressions don’t work as expected
• Ways to improve performance

7.2 Best Practices for Using Regular Expressions

1. Keep regular expressions simple

• Example: avoid overusing .* and leverage ^ (start) and $ (end).

1. Combine with LIKE or FULLTEXT

• Pre-filter with LIKE to narrow down the target before applying REGEXP, reducing processing load.

1. Use REGEXP_REPLACE() for data cleansing

• Example: remove HTML tags, clean up unnecessary spaces.

1. Leverage MySQL 8.0 and later

• Using new functions like REGEXP_LIKE() lets you write more readable SQL.

1. Enforce security measures

• Do not apply user input directly to REGEXP (SQL injection protection).
• Use placeholder for dynamic queries.

7.3 Resources for Further Learning

Official Documentation

• MySQL 8.0 Regular Expression Reference
• MySQL 5.x Regular Expression Basics

7.4 Conclusion