Replaces individual characters with others — each with its own, in a single pass.
TRANSLATE(<text>, <characters>, <replacements>)
Takes the characters from the second argument and replaces each with the character in the same position in the third: the first with the first, the second with the second. Characters that have no pair are simply deleted. Handy for removing stray symbols or replacing several characters at once.
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.
| dashed |
|---|
| 2026-09-13 |
| cleaned |
|---|
| +4407700 |
The third argument has no pairs at all, so the brackets and spaces simply disappear.
| name | phone | digits |
|---|---|---|
| Emma | +44 7700 900123 | 447700900123 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | 447700900456 |
| Noah | +44 7700 900789 | 447700900789 |
| Sophia | NULL | NULL |
It replaces characters, not words: TRANSLATE('cat', 'cat', 'dog') gives dog, but TRANSLATE('act', 'cat', 'dog') gives odg — each letter separately.
Characters with no pair in the third argument are deleted: TRANSLATE('a-b-c', '-', '') removes every hyphen.
To replace whole words, use REPLACE; for patterns — REGEXP_REPLACE.