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

# REGEXP_REPLACE()

## Overview

The `REGEXP_REPLACE()` function replaces all occurrences of a regular expression pattern in a string with a specified replacement string.

## Syntax

The syntax for `REGEXP_REPLACE()` function is as follows:

```sql theme={null}
REGEXP_REPLACE(source_string, pattern, replacement, [flags])
```

## Parameters

* `source_string`: string that we want to perform the replacement on
* `pattern`: POSIX regular expression pattern to match
* `replacement`: replacement string
* `flags`: (optional) string that changes the matching behavior of `REGEXP_REPLACE()` function

The `flags` parameter is an optional string that controls how the function operates. Here is a list of flags supported in Oxla:

* `g`: global replacement. This flag ensures that all occurrences of the pattern are replaced
* `i`: use this flag for case-insensitive matching
* `c`: `REGEXP_REPLACE()` function is case sensitive by default, using the `c` flag has the same effect as using no flags

## Examples

### Basic function usage

In this example, we will focus on using `REGEXP_REPLACE()` function with a basic POSIX regular expression pattern:

```sql theme={null}
SELECT REGEXP_REPLACE('The OXLA supports various data types', 'T[^ ]*', 'We') AS "Replaced_String";
```

By executing the query above, we will get the following output:

```sql theme={null}
 Replaced_String                         
-----------------------------------------
 We OXLA supports various data types     
```

The pattern used was **"T\[^ ]\*"**, which matches any substring that starts with a 'T' character, followed by any number of non-space characters. The function replaces the matched substring with the specified replacement string **"We"**.

### Replacing special characters

This example demonstrates how to replace a non-alphanumeric character in a string with a tilde (\~):

```sql theme={null}
SELECT REGEXP_REPLACE('Hello World!', '[^A-Za-z0-9 ]', '~') AS "Replaced_String";
```

In the above query, the second parameter is a regular expression **“\[^A-Za-z0-9 ]”** that matches any characters that are not uppercase / lowercase letters, digits or spaces. The output for executing the query above will be as follows:

```sql theme={null}
 Replaced String   
-------------------
 Hello World~  	
```

### Flags usage

#### Replacing certain substrings with a single flag defined

This example will focus on using the `REGEXP_REPLACE()` function with a defined flag and replacing certain substrings in a string. For the needs of this section, we will create a sample `quotes` table:

```sql theme={null}
CREATE TABLE quotes (quotes_text text);
INSERT INTO quotes (quotes_text)
VALUES ('Work hard and stay hungry. Lazy people get nowhere in life.'),
       ('An excuse is a way for a LAZY person to feel better.'),
       ('The word LUCKY is how a lazy person describes someone who works hard.');

SELECT quotes_text FROM quotes;
```

By executing the code above, we will get the following output:

```bash theme={null}
                              quotes_text                              
-----------------------------------------------------------------------
 Work hard and stay hungry. Lazy people get nowhere in life.
 An excuse is a way for a LAZY person to feel better.
 The word LUCKY is how a lazy person describes someone who works hard.
(3 rows)
```

Now, we will use the `REGEXP_REPLACE()` function with the `i` flag specifiec to replace all occurrences of the word `lazy` with `active` regardless of the case sensitivity:

```sql theme={null}
SELECT quotes_text, REGEXP_REPLACE(quotes_text, 'lazy', 'active', 'i') AS "New quotes" FROM quotes;
```

In this case, all occurrences of the word `lazy` have been replaced with `active`:

```bash theme={null}
                              quotes_text                              |                               New quotes                                
-----------------------------------------------------------------------+-------------------------------------------------------------------------
 Work hard and stay hungry. Lazy people get nowhere in life.           | Work hard and stay hungry. active people get nowhere in life.
 An excuse is a way for a LAZY person to feel better.                  | An excuse is a way for a active person to feel better.
 The word LUCKY is how a lazy person describes someone who works hard. | The word LUCKY is how a active person describes someone who works hard.
(3 rows)
```

### Specifying one or more flags

Without specifying the `g` flag, `REGEXP_REPLACE()` function replaces only the first occurrence of a substring:

```sql theme={null}
SELECT REGEXP_REPLACE('ab12c', '[0-9]', 'X');
```

```sql theme={null}
 regexp_replace 
----------------
 abX2c
```

In this case, as you can see only the first digit (`1`) was replaced with `X`. By adding the `g` flag, all occurrences are replaced with `X`:

```sql theme={null}
SELECT REGEXP_REPLACE('ab12c', '[0-9]', 'X', 'g');
```

```sql theme={null}
 regexp_replace 
----------------
 abXXc
```

<Info>If you use multiple flags, the last one takes precedence. If you use the `ci` flags, the regex will be case-insensitive, while using the `ic` flags it will be case-sensitive</Info>

## Restrictions

* The function returns `NULL` if there are no input rows or `NULL` values
* If the regular expression pattern isn't found in the string, the `REGEXP_REPLACE()` function returns the original string
* `i` and `c` flags shouldn't be used with each other