# REGEXP\_REPLACE

Searches a string for the first occurrence of a [regular expression pattern](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions.md) and replaces that occurrence with a replacement string.

If no occurrences are found, the search string is returned as is.

If you call `REGEXP_REPLACE` and specify `g` in the `parameters` argument, the function performs a search and replace of all occurrences of a regular expression pattern with a replacement string.

`REGEXP_REPLACE` supports the [extended and advanced regular expression formats](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions.md). You can set the global variable `regexp_format` to specify which format you want to use.

## Syntax

```
REGEXP_REPLACE (<source_string>, <pattern>, <replace_string> [, <parameters>]))

```

* **source\_string**: A string expression, such as a column name, to be searched.

* **pattern**: A string representing a SQL standard regular expression.

* **replace\_string**: A string that replaces the first occurrence of the regular expression pattern. If you specify `g` in the `parameters` argument, `replace_string` replaces all occurrences of the regular expression pattern.

* **parameters**: Optional. If present, has one or more of the following values.

  * `c`: Performs case-sensitive matching.
  * `i`: Performs case-insensitive matching.
  * `g`: Replaces all occurrences of a regular expression pattern with a replacement string.
  * If `c` or `i` is not specified, performs case-sensitive matching.

## Remarks

* Refer to [Regular Expression Functions](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions.md) for details about error handling.

## Examples

The following examples demonstrate how `REGEXP_REPLACE` works when the global variable `regexp_format` is set to `'extended'`.

| Call to`REGEXP_REPLACE`                                | Result      | Comments                                                                        |
| ------------------------------------------------------ | ----------- | ------------------------------------------------------------------------------- |
| `SELECT REGEXP_REPLACE('aadog','.dog','cat');`         | `acat`      | Replaces the first occurrence of any character followed by`dog`with`cat`.       |
| `SELECT REGEXP_REPLACE('aadog','[:alpha:]dog','cat');` | `acat`      | Replaces the first occurrence of any alpha character followed by`dog`with`cat`. |
| `SELECT REGEXP_REPLACE('aadog','\\wdog','cat');`       | `acat`      | Replaces the first occurrence of any alpha character followed by`dog`with`cat`. |
| `SELECT REGEXP_REPLACE('catcatcat','c','b');`          | `batcatcat` | Replaces the first occurrence of`c`with`b`.                                     |
| `SELECT REGEXP_REPLACE('CatCatCat','c','b','i');`      | `batCatCat` | Replaces the first occurrence of`c`with`b`. Does a case-insensitive search.     |
| `SELECT REGEXP_REPLACE('CatCatCat','c','b','ig');`     | `batbatbat` | Replaces all occurrences of`c`with`b`. Does a case-insensitive search.          |

The following examples demonstrate how `REGEXP_INSTR` works when the global variable `regexp_format` is set to `'advanced'`.

| Call to`REGEXP_REPLACE`                                        | Result         | Comments                                                                                                                                      |
| -------------------------------------------------------------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------- |
| `SELECT REGEXP_REPLACE('dog58','\\d','A');`                    | `dogA8`        | Replaces the first occurrence of any digit with`A`.                                                                                           |
| `SELECT REGEXP_REPLACE('%26dog','\\w','B');`                   | `%B6dog`       | Replaces the first occurrence of any alpha-numeric character with`B`.                                                                         |
| `SELECT REGEXP_REPLACE('dog58','\\D','A');`                    | `Aog58`        | Replaces the first occurrence of any non-digit with`A`.                                                                                       |
| `SELECT REGEXP_REPLACE('%26dog','\\W','B');`                   | `B26dog`       | Replaces the first occurrence of any non alpha-numeric character with`B`.                                                                     |
| `SELECT REGEXP_REPLACE('apple banana','(.+) (.+)','\\2 \\1');` | `banana apple` | Uses backreferences. The nth group of parentheses is the nth backreference. Replaces the first backreference with the second, and vice-versa. |

## Using `REGEXP_REPLACE` With a Table

The following example shows how to call `REGEXP_REPLACE` once per table row. This example uses extended regular expression matching. Advanced regular expression matching could be used as well.

```sql
SET GLOBAL regexp_format = 'extended';

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE regexample_table(id INT, text_field TEXT);

INSERT INTO regexample_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'), 
(4, 'abcthe'), (5, 'thethe');

SELECT id, text_field, REGEXP_REPLACE(text_field, '..the', 'to') FROM regexample_table
ORDER BY id;

```

```output

+------+------------+-------------------------------------------+
| id   | text_field | REGEXP_REPLACE(text_field, '..the', 'to') |
+------+------------+-------------------------------------------+
|    1 | the        | the                                       |
|    2 | athe       | athe                                      |
|    3 | abthe      | to                                        |
|    4 | abcthe     | ato                                       |
|    5 | thethe     | tto                                       |
+------+------------+-------------------------------------------+

```

***

Modified at: September 25, 2025

Source: [/db/v9.1/reference/sql-reference/regular-expression-functions/regexp-replace/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions/regexp-replace/)

(An index of the documentation is available at /llms.txt)