🔬 MySQL REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR() – Regex Functions in MySQL 8+

🧲 Introduction – Why Use REGEXP Functions in MySQL?

With MySQL 8.0, advanced regex functions were introduced for powerful text search, extraction, replacement, and validation using POSIX regular expressions. These include:

• 🔍 REGEXP_INSTR() – Returns position of regex match
• ✅ REGEXP_LIKE() – Returns true/false if match exists
• 🔁 REGEXP_REPLACE() – Replaces part of string using regex
• 🔍 REGEXP_SUBSTR() – Extracts substring that matches regex

These functions extend the capability of basic REGEXP and are ideal for data validation, transformation, and search filtering.

🧪 1. REGEXP_INSTR() – Get Match Position

🔹 Syntax

REGEXP_INSTR(expr, pattern [, position [, occurrence [, return_option [, match_type]]]])

🔹 Example – Position of Digits

SELECT REGEXP_INSTR('abc123xyz', '[0-9]+') AS match_pos;

✅ Returns 4 → First number starts at position 4

🔹 Example – 2nd Occurrence of a Word

SELECT REGEXP_INSTR('cat dog cat bird', 'cat', 1, 2);

✅ Returns 9 → Position of second cat

✅ 2. REGEXP_LIKE() – Boolean Pattern Check

🔹 Syntax

REGEXP_LIKE(expr, pattern [, match_type])

🔹 Example – Check Email Format

SELECT REGEXP_LIKE('test@gmail.com', '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.com$') AS is_valid;

✅ Returns 1 if it matches the pattern

🔹 Use in WHERE Clause

SELECT * FROM users WHERE REGEXP_LIKE(email, '@gmail\\.com$');

✅ Filters users with Gmail addresses

🔁 3. REGEXP_REPLACE() – Replace with Regex Pattern

🔹 Syntax

REGEXP_REPLACE(expr, pattern, replacement [, position [, occurrence [, match_type]]])

🔹 Example – Remove Digits

SELECT REGEXP_REPLACE('user123profile', '[0-9]+', '');

✅ Output: 'userprofile'

🔹 Replace First Occurrence Only

SELECT REGEXP_REPLACE('cat cat cat', 'cat', 'dog', 1, 1);

✅ Output: 'dog cat cat'

🔍 4. REGEXP_SUBSTR() – Extract Matching Substring

🔹 Syntax

REGEXP_SUBSTR(expr, pattern [, position [, occurrence [, match_type]]])

🔹 Example – Extract First Word

SELECT REGEXP_SUBSTR('Hello world 2024', '[A-Za-z]+');

✅ Output: 'Hello'

🔹 Get Numeric Part from String

SELECT REGEXP_SUBSTR('Order #A10123', '[0-9]+');

✅ Output: '10123'

🧬 5. Match Types (Optional)

Example:

SELECT REGEXP_LIKE('abc', 'A', 'c'); -- Case-sensitive: returns 0

📘 Comparison Table

🚀 Real-World Use Cases

📌 Summary – Recap & Next Steps

MySQL’s advanced REGEXP_ functions allow for searching, validating, replacing, and extracting text using regular expressions—far beyond basic LIKE and REGEXP.

🔍 Key Takeaways

• REGEXP_INSTR() – find where a match occurs
• REGEXP_LIKE() – check if a match exists (boolean)
• REGEXP_REPLACE() – modify matched substrings
• REGEXP_SUBSTR() – extract substring that matches pattern
• Optional match types like 'c', 'i', 'm' allow fine control

⚙️ Real-World Relevance

These functions power form validation, search engines, log filtering, data cleanup, and text analytics in MySQL 8+ environments.

❓ FAQ – MySQL Regex Functions

❓ Are these REGEXP functions available in MySQL 5.x?

❌ No. These functions require MySQL 8.0+.

❓ Is REGEXP_LIKE faster than REGEXP in WHERE?

✅ Yes, REGEXP_LIKE() is optimized for boolean evaluations.

❓ Can I replace multiple occurrences in REGEXP_REPLACE()?

✅ Yes. Leave occurrence as default (0) or set it to a high value.

❓ Is REGEXP case-sensitive?

✅ By default it’s case-insensitive. Use 'c' flag for case sensitivity.

❓ Can I use regex on numeric columns?

❌ No. You must cast numbers to strings using CAST(num AS CHAR).