> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for REGEXP_REPLACE functions

# REGEXP_REPLACE

Matches a pattern in the input string and replaces the first matched portion (from the left) with the specified replacement.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
REGEXP_REPLACE(<input>, <pattern>, <replacement>)
```

## REGEXP\_REPLACE\_ALL

Matches a pattern in the input string and replaces all matched portions with the specified replacement.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
REGEXP_REPLACE_ALL(<input>, <pattern>, <replacement>)
```

## Parameters

| Parameter       | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | Supported input types |
| :-------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<input>`       | The string to search for a matching pattern                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | `TEXT`                |
| `<pattern>`     | An [RE2 regular expression](https://github.com/google/re2/wiki/Syntax) for matching with the string input.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | `TEXT`                |
| `<replacement>` | The string to replace the matching pattern found in the input. This argument can include the following special sequences: <br />\* `\&` - To indicate that the substring matching the entire pattern should be inserted.<br />\* `\n` - Where *n* is a digit from 1 to 9, to indicate that the substring matching the n'th capturing group (parenthesized subexpression) of the pattern should be inserted. If pattern doesn't have *n* capturing groups, the `\n` is ignored.<br />\* `\\` - results in a single \<br/>\* `\c` - Specifies for any other character, c results in the same sequence \c<br /> Note, that for string literals the above escaping rules apply *after* string literals escaping rules for `\`. See examples below. | `TEXT`                |

If any of the arguments to these functions is `NULL`, the return value is `NULL`.

### Return Type

`TEXT`

## Examples

Replace first occurrence of `!` with `!!!`:

<div className="query-window">
  ```
  SELECT REGEXP_REPLACE('Hello, world!', '!', '!!!');
  ```

  | regexp\_replace <span>text</span> |
  | :-------------------------------- |
  | Hello, world!!!                   |

  <p><span>Rows: 1</span><span>Execution time: 5.23ms</span></p>
</div>

Remove leading and trailing spaces:

<div className="query-window">
  ```
  SELECT REGEXP_REPLACE_ALL('     Hello world ! ', '^[ ]+|[ ]+$', '');
  ```

  | regexp\_replace\_all <span>text</span> |
  | :------------------------------------- |
  | Hello world !                          |

  <p><span>Rows: 1</span><span>Execution time: 5.17ms</span></p>
</div>

Duplicate every character:

<div className="query-window">
  ```
  SELECT REGEXP_REPLACE_ALL('Hello, World!', '.', '\&\&');
  ```

  | regexp\_replace\_all <span>text</span> |
  | :------------------------------------- |
  | HHeelllloo,,  WWoorrlldd!!             |

  <p><span>Rows: 1</span><span>Execution time: 5.53ms</span></p>
</div>

Mask email address by leaving first character only (Note: this is for illustrative purposes only, the email matching pattern is too simplistic):

<div className="query-window">
  ```
  SELECT REGEXP_REPLACE(email, '(\w)[\w\.]+@([\w]+\.)+([\w]+)', '\1***@\2\3') AS masked_email
  FROM UNNEST([
    'matt123@hotmail.com',
    'joe.doe@gmail.com',
    '12345@www.atg.wa.gov'
  ]) email
  ORDER BY masked_email;
  ```

  | masked\_email <span>text</span> |
  | :------------------------------ |
  | 1\*\*\*@wa.gov                  |
  | j\*\*\*@gmail.com               |
  | m\*\*\*@hotmail.com             |

  <p><span>Rows: 3</span><span>Execution time: 1.54ms</span></p>
</div>

Convert dates into US format:

<div className="query-window">
  ```
  SELECT REGEXP_REPLACE(event_date::TEXT, '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1') AS converted_date
  FROM UNNEST([
    DATE '1970-08-07',
    DATE '2000-04-22',
    DATE '2002-07-25',
    DATE '2010-11-11'
  ]) event_date
  ORDER BY converted_date;
  ```

  | converted\_date <span>text</span> |
  | :-------------------------------- |
  | 04/22/2000                        |
  | 07/25/2002                        |
  | 08/07/1970                        |
  | 11/11/2010                        |

  <p><span>Rows: 4</span><span>Execution time: 1.53ms</span></p>
</div>