top of page
CerebroSQL

REGEXP_REPLACE

Database: MySQL

68747470733a2f2f7374796c65732e7265646469746d656469612e636f6d2f74355f32716d366b2f7374796c65

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Replaces occurrences in the string expr that match the regular
expression specified by the pattern pat with the replacement string
repl, and returns the resulting string. If expr, pat, or repl is NULL,
the return value is NULL.

REGEXP_REPLACE() takes these optional arguments:

o pos: The position in expr at which to start the search. If omitted,
the default is 1.

o occurrence: Which occurrence of a match to replace. If omitted, the
default is 0 (which means "replace all occurrences").

o match_type: A string that specifies how to perform matching. The
meaning is as described for REGEXP_LIKE().

Prior to MySQL 8.0.17, the result returned by this function used the
UTF-16 character set; in MySQL 8.0.17 and later, the character set and
collation of the expression searched for matches is used. (Bug #94203,
Bug #29308212)

For additional information about how matching occurs, see the
description for REGEXP_LIKE().

URL: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Example

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

bottom of page