Топ-100
 
CerebroSQL

REGEXP_REPLACE

Database: MySQL
Topic

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 |
+----------------------------------------------------+