TRANSLATE()
SQL · String Functions
Theory
TRANSLATE(string, from_chars, to_chars) replaces single characters using a position-by-position mapping: the 1st character of from_chars becomes the 1st character of to_chars, the 2nd becomes the 2nd, and so on. TRANSLATE('abc123', '123', 'xyz') returns 'abcxyz'. All mappings happen in one pass, so TRANSLATE(date_text, '/.', '--') turns both '12/04/2025' and '12.04.2025' into '12-04-2025'.
This is different from REPLACE, which swaps whole substrings. In Postgres and Oracle, characters in from_chars with no partner in to_chars are deleted, so TRANSLATE(phone, '- ', '') would remove dashes and spaces (Oracle needs a trick because an empty string is NULL there). SQL Server's TRANSLATE requires both lists to be the same length. MySQL and SQLite have no TRANSLATE, so you chain REPLACE calls, one per character, as the diagram does.
Why we use it
Cleaning often means swapping many single characters at once: different date separators, accented letters to plain ones, masking every digit. TRANSLATE does all of them in one call instead of a long chain of REPLACE calls.
When to use it
Use TRANSLATE for one-to-one character swaps or deleting a set of characters. Use REPLACE when the text to change is longer than one character (like '.com'), and chain REPLACE where TRANSLATE is not available.
How it works
- Take the string, from_chars and to_chars
- Read the next character of the string
- Is it in from_chars? Yes: Swap for matching to_char (or drop). No: Keep it unchanged.
- Repeat to the end of the string
- Return the translated string
Syntax
TRANSLATE(string, from_chars, to_chars)Example
SELECT TRANSLATE('abc123', '123', 'xyz');
-- 'abcxyz'Key points
- Character-level substitution, unlike REPLACE()'s substring matching
- Mapping is by position: 1st from-char becomes 1st to-char
- Postgres/Oracle delete from-chars that have no partner
- Available in Oracle, Postgres, SQL Server and Snowflake, not SQLite or MySQL
- Emulate with one REPLACE per character where missing