REGEXP_REPLACE

Syntax

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

Description

The REGEXP_REPLACE function replaces the substrings in the string expr that match the pattern pat with the string specified by repl and returns the resulting string. If expr, pat, or repl is NULL, the function returns NULL.

• pos specifies the starting position in the string expr for the search. The default value is 1.

• occurrence specifies the occurrence of the match to be replaced with the string specified by repl. The default value is 0, which indicates that all matched substrings are replaced.

• match_type specifies the matching rules. For more information about the matching rules, see REGEXP_LIKE.

Examples

SELECT REGEXP_REPLACE('OceanBase', 'a', '2');
+---------------------------------------+
| REGEXP_REPLACE('OceanBase', 'a', '2') |
+---------------------------------------+
| Oce2nB2se                             |
+---------------------------------------+
1 row in set (0.001 sec)

SELECT REGEXP_REPLACE('OceanBase', 'a', '2',7);
+-----------------------------------------+
| REGEXP_REPLACE('OceanBase', 'a', '2',7) |
+-----------------------------------------+
| OceanB2se                               |
+-----------------------------------------+
1 row in set (0.001 sec)

SELECT REGEXP_REPLACE('OceanBase', 'a', '2',1,2);
+-------------------------------------------+
| REGEXP_REPLACE('OceanBase', 'a', '2',1,2) |
+-------------------------------------------+
| OceanB2se                                 |
+-------------------------------------------+
1 row in set (0.001 sec)