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

# PostgreSQL REPLACE() Function

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `REPLACE()` function to replace a substring with a new one.

## Introduction to PostgreSQL REPLACE() function

The `REPLACE()` function replaces all occurrences of a substring with a new one in a string.

Here's the syntax of the PostgreSQL `REPLACE()` function:

```sql
REPLACE(source, from_text, to_text);
```

The `REPLACE()` function accepts three arguments:

- `source`: This is an input string that you want to replace.
- `from_text`: This is the substring that you want to search and replace. If the `from_text` appears multiple times in the `source` string, the function will replace all the occurrences.
- `to_text`: This is the new substring that you want to replace the `from_text`.

## PostgreSQL REPLACE() function examples

Let's explore some examples of using the `REPLACE()` function.

### 1) Basic PostgreSQL REPLACE() function example

The following example uses the `REPLACE()` function to replace the string `'A'` in the string `'ABC AA'` with the string `'Z'`:

```sql
SELECT REPLACE ('ABC AA', 'A', 'Z');
```

Output:

```text
 replace
---------
 ZBC ZZ
(1 row)
```

In this example, the `REPLACE()` function replaces all the characters `'A'` with the character `'Z'` in a string.

### 2) Using the PostgreSQL REPLACE() function with table data

If you want to search and replace a substring in a table column, you use the following syntax:

```sql
UPDATE
  table_name
SET
  column_name = REPLACE(column, old_text, new_text)
WHERE
  condition;
```

Let's see the following example.

First, [create a new table](../postgresql-tutorial/postgresql-create-table) called `posts` that has three columns `id`, `title`, and `url`:

```sql
CREATE TABLE posts(
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(255) NOT NULL
);

INSERT INTO posts(title, url)
VALUES
('PostgreSQL Tutorial', 'http://neon.tech/postgresql'),
('PL/pgSQL', 'http://neon.tech/postgresql/postgresql-plpgsql/'),
('PostgreSQL Administration
', 'http://neon.tech/postgresql/postgresql-administration/')
RETURNING *;
```

Output:

```
 id |           title           |                             url
----+---------------------------+--------------------------------------------------------------
  1 | PostgreSQL Tutorial       | http://neon.tech/postgresql
  2 | PL/pgSQL                  | http://neon.tech/postgresql/postgresql-plpgsql/
  3 | PostgreSQL Administration+| http://neon.tech/postgresql/postgresql-administration/
    |                           |
(3 rows)


INSERT 0 3
```

Second, replace the `http` in the `url` column with the `https` using the `REPLACE()` function:

```sql
UPDATE posts
SET url = REPLACE(url, 'http','https');
```

Output:

```sql
UPDATE 3
```

The output indicates that three rows were updated.

Third, verify the update by retrieving data from the `customer` table:

```sql
SELECT * FROM posts;
```

Output:

```
 id |           title           |                              url
----+---------------------------+---------------------------------------------------------------
  1 | PostgreSQL Tutorial       | https://neon.com/postgresql
  2 | PL/pgSQL                  | https://neon.com/postgresql/postgresql-plpgsql/
  3 | PostgreSQL Administration+| https://neon.com/postgresql/postgresql-administration/
    |                           |
(3 rows)
```

The output indicates that the `http` in the `url` column were replaced by the `https`.

## Summary

- Use the PostgreSQL `REPLACE()` function to replace all occurrences of a substring in a string with another 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)
- [REGEXP_REPLACE](https://neon.com/postgresql/postgresql-string-functions/regexp_replace)
- [REPEAT](https://neon.com/postgresql/postgresql-string-functions/postgresql-repeat)
- [REVERSE](https://neon.com/postgresql/postgresql-string-functions/postgresql-reverse)
- [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)