How to Use REGEXP_REPLACE in MySQL: Syntax and Examples

In MySQL, REGEXP_REPLACE is used to replace parts of a string that match a regular expression pattern with a new substring. The function syntax is REGEXP_REPLACE(source_string, pattern, replacement), where it searches source_string for matches to pattern and replaces them with replacement.

Syntax

The REGEXP_REPLACE function has three main parts:

• source_string: The original text where you want to replace parts.
• pattern: The regular expression pattern to find in the source string.
• replacement: The text that will replace the matched parts.

The function returns a new string with all matches replaced.

sql

REGEXP_REPLACE(source_string, pattern, replacement)

Example

This example shows how to replace all digits in a string with the character '#'.

sql

SELECT REGEXP_REPLACE('My phone number is 123-456-7890', '[0-9]', '#') AS replaced_string;

Output

replaced_string
My phone number is ###-###-####

Common Pitfalls

Common mistakes when using REGEXP_REPLACE include:

• Using patterns without proper escaping, which can cause errors or unexpected results.
• Expecting the function to modify the original string in place; it returns a new string instead.
• Forgetting that the pattern is a regular expression, so special characters need to be handled carefully.

sql

/* Wrong: Using unescaped dot to match a literal dot */
SELECT REGEXP_REPLACE('file.txt', '.', '_') AS wrong_replace;

/* Right: Escape dot to match literal dot */
SELECT REGEXP_REPLACE('file.txt', '\\.', '_') AS correct_replace;

Output

wrong_replace
_____________
correct_replace
file_txt

Quick Reference

Parameter	Description
source_string	The text to search and replace in
pattern	Regular expression pattern to find
replacement	Text to replace matched parts

Key Takeaways

Use REGEXP_REPLACE to replace parts of a string matching a regex pattern in MySQL.

Always escape special regex characters properly in the pattern.

REGEXP_REPLACE returns a new string; it does not change the original data.

Test your regex patterns to avoid unexpected replacements.

Use REGEXP_REPLACE for flexible text replacements beyond simple string matching.