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

# PostgreSQL REGEXP_REPLACE() Function

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