Replaces every occurrence of one piece of text in a string with another.
REPLACE(<text>, <from>, <to>)
Searches the text for a substring and replaces each occurrence, not just the first. To remove a substring completely, replace it with an empty string ''. Often used to clean up data, for example to remove spaces from phone numbers or swap an old domain in links.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| result |
|---|
| I love PostgreSQL |
| result |
|---|
| a, b, c, d |
| name | phone | compact_phone |
|---|---|---|
| Emma | +44 7700 900123 | +447700900123 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | +447700900456 |
| Noah | +44 7700 900789 | +447700900789 |
| Sophia | NULL | NULL |
Case matters: REPLACE('Hello', 'hello', 'Hi') replaces nothing. To ignore case — REGEXP_REPLACE(<text>, 'hello', 'Hi', 'gi').
It replaces a whole substring, not individual characters. To remove several different characters at once, use TRANSLATE: TRANSLATE('+44 (0) 7700', '() ', '') gives +4407700.
If any argument is NULL, the result is NULL too: REPLACE('abc', 'b', NULL).