> ## 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":"github-light","dark":"github-dark"}}
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":"github-light","dark":"github-dark"}}
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 `!!!`

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT REGEXP_REPLACE('Hello, world!', '!', '!!!');
```

**Returns**: `'Hello, world!!!'`

Remove leading and trailing spaces

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT REGEXP_REPLACE_ALL('     Hello world ! ', '^[ ]+|[ ]+$', '');
```

**Returns**: `'Hello world !'`

Duplicate every character

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT REGEXP_REPLACE_ALL('Hello, World!', '.', '\&\&')
```

**Returns**: `'HHeelllloo,,  WWoorrlldd!!'`

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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT REGEXP_REPLACE(email, '(\w)[\w\.]+@([\w]+\.)+([\w]+)', '\1***@\2\3')
FROM UNNEST([
  'matt123@hotmail.com',
  'joe.doe@gmail.com',
  '12345@www.atg.wa.gov'
]) email
```

**Returns**:

```
'm***@hotmail.com'
'j***@gmail.com'
'1***@www.atg.wa.gov'
```

Convert dates into US format

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT REGEXP_REPLACE(event_date::TEXT, '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1')
FROM UNNEST([
  DATE '1970-08-07',
  DATE '2000-04-22',
  DATE '2002-07-25',
  DATE '2010-11-11'
]) event_date
```

**Returns**

```
08/07/1970
04/22/2000
07/25/2002
11/11/2010
```