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).