MySQL REGEXP_REPLACE() Function

The MySQL REGEXP_REPLACE() function replaces occurrences in the string that match the regular expression specified by the pattern with the replacement string replace, and returns the resulting string. If the string or pattern is NULL, the return value will be NULL.

By default, REGEXP_REPLACE() function performs a case-insensitive match, except when used with binary strings.

Syntax

REGEXP_REPLACE(string, pattern, replace, 
               position, occurrence, match_type)

Parameters

`string`	`Required.` Specify the string to search.
`pattern`	`Required.` Specify the regular expression matching information. See the table below for pattern syntax.
`replace`	`Required.` Specify the replacement substring.
`position`	`Optional.` Specify the position in the string at which to start the search. Default is 1.
`occurrence`	`Optional.` Specify which occurrence of a match to replace. Default is 0 (which means "replace all occurrences").
`match_type`	`Optional.` Specify how to perform matching. It can take following values: c - Case-sensitive matching i - Case-insensitive matching m - Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression n - The . character matches line terminators. The default is for . matching to stop at the end of a line u - Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators For additional information about this parameter, see the examples of REGEXP_LIKE() function.

Regular Expression Patterns

Symbol	Description
^	Matches the beginning of a string.
$	Matches the end of a string.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
\|	Used like an "OR" to specify more than one alternative.
[ ]	Matches any single character specified within [].
[^ ]	Matches any single character that is not specified within [].
-	Represents a range of characters.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.
[..]	Matches one collation element that can be more than one character.
[=character_class=]	Represents an equivalence class. It matches all characters with the same collation value, including itself.
[:character_class:]	Represents a character class that matches all characters belonging to that class.

List of standard class name

Character Class Name	Meaning
alnum	Alphanumeric characters
alpha	Alphabetic characters
blank	Whitespace characters
cntrl	Control characters
digit	Digit characters
graph	Graphic characters
lower	Lowercase alphabetic characters
print	Graphic or space characters
punct	Punctuation characters
space	Space, tab, newline, and carriage return
upper	Uppercase alphabetic characters
xdigit	Hexadecimal digit characters

Example: REGEXP_REPLACE() examples

The example below shows the usage of REGEXP_REPLACE() function.

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('<html><head><title>title</title><body>body</body></htm>', '<.+?>','');
Result: 'titlebody'

Example: Match on more than one alternative

Consider a database table called Employee with the following records:

EmpID	Name	City	Age
1	John Smith	London	25
2	Marry Knight	New York	24
3	Jo Williams	Paris	27
4	Smith Fischer	Amsterdam	30
5	Aman Gupta	New Delhi	28

The below query is used to replace all vowels in the records of City column with '@'. To specify more than one alternative in the pattern, | is used.

SELECT *, 
REGEXP_REPLACE(City, 'a|e|i|o|u', '@') AS Replaced_City
FROM Employee

This will produce a result similar to:

EmpID	Name	City	Age	Replaced_City
1	John Smith	London	25	L@nd@n
2	Marry Knight	New York	24	N@w Y@rk
3	Jo Williams	Paris	27	P@r@s
4	Smith Fischer	Amsterdam	30	@mst@rd@m
5	Aman Gupta	New Delhi	28	N@w D@lh@

Example: Matching on words

Consider the above discussed table. The below query is used to replace the first word (first name) from the Name column with 'Mr.'. To specify a range of characters, [ ] is used

SELECT *, 
REGEXP_REPLACE(Name, '[a-z]+', 'Mr.', 1, 1) AS ReplacedName
FROM Employee

This will produce a result similar to:

EmpID	Name	City	Age	ReplacedName
1	John Smith	London	25	Mr. Smith
2	Marry Knight	New York	24	Mr. Knight
3	Jo Williams	Paris	27	Mr. Williams
4	Smith Fischer	Amsterdam	30	Mr. Fischer
5	Aman Gupta	New Delhi	28	Mr. Gupta

❮ MySQL Functions