Syntax
REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
Purpose
For substrings in the expr expression that match the pattern pat, this function replaces the substrings with repl and returns the resulting string. If str, pat, or repl is NULL, the function returns NULL.
posindicates the start position for character search in theexprexpression. The default value is1.occurrenceindicates which substring that matches the pattern should be replaced withrepl. The default value0indicates that all matching substrings are replaced.match_typeindicates the matching rule. For information about matching rules, see REGEXP_LIKE.
Examples
obclient> SELECT REGEXP_REPLACE('OceanBase', 'a', '2');
+---------------------------------------+
REGEXP_REPLACE('OceanBase', 'a', '2')
+---------------------------------------+
Oce2nB2se
+---------------------------------------+
1 row in set (0.00 sec)
obclient> SELECT REGEXP_REPLACE('OceanBase', 'a', '2',7);
+-----------------------------------------+
REGEXP_REPLACE('OceanBase', 'a', '2',7)
+-----------------------------------------+
OceanB2se
+-----------------------------------------+
1 row in set (0.00 sec)
obclient> SELECT REGEXP_REPLACE('OceanBase', 'a', '2',1,2);
+-------------------------------------------+
REGEXP_REPLACE('OceanBase', 'a', '2',1,2)
+-------------------------------------------+
OceanB2se
+-------------------------------------------+
1 row in set (0.01 sec)