# **REGEXP_REPLACE()**

## **Function Description**

`REGEXP_REPLACE()` is used to replace the string matching the given regular expression pattern with the specified new string.

## **grammar**

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

### Parameter definition

- `expr` is the string to be replaced.

- `pat` This is a regular expression, and the function looks for all strings that match this pattern.

- `repl` is a replacement string that replaces the matching string found.

- `pos`: This is an optional parameter that specifies where to start the search. The default value is 1, indicating that the search starts from the start position of the string.

- `occurrence`: This is an optional parameter that specifies the position to replace the match. The default value is 1, indicating that the first match is returned.

- The `match_type` parameter is an optional string that specifies the way to match. This parameter can be composed of the following characters, each character specifies a matching method, and the order of characters does not affect the result:

    + `'c': case sensitive for matching (i.e. uppercase and lowercase letters are treated as different characters). By default, matches are case sensitive.
    + `'i'`: matches insensitively (i.e. uppercase and lowercase letters are treated as the same characters).
    + `'n'`: Allows the `.` symbol to match newlines. By default, the `.` symbol does not match newlines.
    + `'m': Treat a string as multiple lines. That is,`^` matches the beginning of the string or the beginning of any line, and `$` matches the end of the string or the end of any line. By default, `^` only matches the beginning of the string, and `$` only matches the end of the string.
    + `'u': Treats the pattern as a UTF-8 string. By default, the pattern is treated as a byte string.

## **Example**

```text
mysql> SELECT REGEXP_REPLACE('Hello, World!', 'World', 'Universe');
+-----------------------------------------------------------+
| regexp_replace(Hello, World!, World, Universe) |
+-----------------------------------------------------------+
| Hello, Universe! |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_REPLACE('Cat Dog Cat Dog Cat','Cat', 'Tiger') 'Result';
+--------------------------------+
| Results |
+--------------------------------+
| Tiger Dog Tiger Dog Tiger |
+--------------------------------+
1 row in set (0.01 sec)
```