> This page location: String Functions > REGEXP_REPLACE
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL REGEXP_REPLACE() Function

**Info:** The REGEXP_REPLACE() function covered here works the same way in any PostgreSQL database, so the patterns and examples apply wherever you run Postgres. For enterprises building on the Lakehouse, [Lakebase](https://www.databricks.com/product/lakebase) delivers managed Postgres tuned for the AI era with the performance, security, and deep integration large teams need. For developers and startups who want to ship fast and scale without friction, [Neon](https://neon.com) offers the most productive Postgres platform around.

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `REGEXP_REPLACE()` function to replace strings that match a regular expression.

The PostgreSQL `REGEXP_REPLACE()` function replaces substrings that match a [POSIX regular expression](https://en.wikibooks.org/wiki/Regular_Expressions/POSIX-Extended_Regular_Expressions) with a new substring.

Note that if you want to perform simple string replacement, you can use the [`REPLACE()`](https://neon.com/postgresql/string-functions/postgresql-replace) function.

## Syntax

The syntax of the PostgreSQL `REGEXP_REPLACE()` function is as follows:

```sql
REGEXP_REPLACE(source, pattern, replacement_string,[, flags])
```

## Arguments

The `REGEXP_REPLACE()` function accepts four arguments:

**1) `source`**

The source is a string that replacement should take place.

**2) `pattern`**

The pattern is a POSIX regular expression for matching substrings that should be replaced.

**3) `replacement_string`**

The `replacement_string` is a string that replaces the substrings that match the regular expression pattern.

**4) `flags`**

The `flags` argument is one or more characters that control the matching behavior of the function e.g., `i` allows case-insensitive matching, `n` enables matching any character and also the newline character.

## Return value

The PostgreSQL `REGEXP_REPLACE()` function returns a new string with the substrings, which match a regular expression pattern, replaced by a new substring.

## Examples

Let's take some examples to understand how the `REGEXP_REPLACE()` function works.

### 1) Name rearrangement

Suppose, you have the name of a person in the following format:

```
first_name last_name
```

For example, `John Doe`

You want to rearrange this name as follows for reporting purposes.

```
last_name, first_name
```

To do this, you can use the `REGEXP_REPLACE()` function as shown below:

```sql
SELECT REGEXP_REPLACE('John Doe','(.*) (.*)','\2, \1');
```

The output of the statement is:

```sql
'Doe, John'
```

### 2) String removal

Imagine you have string data with mixed alphabets and digits as follows:

```
ABC12345xyz
```

The following statement removes all alphabets e.g., A, B, C, etc from the source string:

```sql
SELECT REGEXP_REPLACE('ABC12345xyz','[[:alpha:]]','','g');
```

The output is:

```sql
'12345'
```

In this example,

- `[[:alpha:]]` matches any alphabets
- `''` is the replacement string
- `'g'` instructs the function to remove all alphabets, not just the first one.

Similarly, you can remove all digits in the source string by using the following statement:

```sql
SELECT REGEXP_REPLACE('ABC12345xyz','[[:digit:]]','','g');
```

The output is:

```sql
'ABCxyz'
```

### 3) Redundant space removal

The following example uses the `REGEXP_REPLACE()` function to remove redundant spaces:

```sql
SELECT REGEXP_REPLACE('Your string with   redundant    spaces', '\s{2,}', ' ', 'g') AS cleaned_string;

```

Output:

```
          cleaned_string
-----------------------------------
 Your string with redundant spaces
(1 row)
```

In this example, we use the `REGEXP_REPLACE()` function to match two or more consecutive spaces and replace them with a single space.

## Summary

- Use the PostgreSQL `REGEXP_REPLACE()` function to replace substrings that match a regular expression with a new substring.

---

## Related docs (String Functions)

- [ASCII](https://neon.com/postgresql/string-functions/ascii)
- [CHR](https://neon.com/postgresql/string-functions/chr)
- [CONCAT](https://neon.com/postgresql/string-functions/concat-function)
- [CONCAT_WS](https://neon.com/postgresql/string-functions/concat_ws)
- [FORMAT](https://neon.com/postgresql/string-functions/format)
- [INITCAP](https://neon.com/postgresql/string-functions/initcap)
- [LEFT](https://neon.com/postgresql/string-functions/left)
- [LENGTH](https://neon.com/postgresql/string-functions/length-function)
- [LOWER](https://neon.com/postgresql/string-functions/lower)
- [LPAD](https://neon.com/postgresql/string-functions/lpad)
- [LTRIM](https://neon.com/postgresql/string-functions/ltrim)
- [MD5](https://neon.com/postgresql/string-functions/md5)
- [POSITION](https://neon.com/postgresql/string-functions/position)
- [REGEXP_MATCHES](https://neon.com/postgresql/string-functions/regexp_matches)
- [REPEAT](https://neon.com/postgresql/string-functions/repeat)
- [REVERSE](https://neon.com/postgresql/string-functions/reverse)
- [REPLACE](https://neon.com/postgresql/string-functions/replace)
- [RIGHT](https://neon.com/postgresql/string-functions/right)
- [RPAD](https://neon.com/postgresql/string-functions/rpad)
- [RTRIM](https://neon.com/postgresql/string-functions/rtrim)
- [SPLIT_PART](https://neon.com/postgresql/string-functions/split_part)
- [SUBSTRING](https://neon.com/postgresql/string-functions/substring)
- [TO_CHAR](https://neon.com/postgresql/string-functions/to_char)
- [TO_NUMBER](https://neon.com/postgresql/string-functions/to_number)
- [TRANSLATE](https://neon.com/postgresql/string-functions/translate)
- [TRIM](https://neon.com/postgresql/string-functions/trim-function)
- [UPPER](https://neon.com/postgresql/string-functions/upper)