SQL TRANSLATE: Per-Character Mapping, Deletion and Transliteration

TRANSLATE takes a string and two equally meaningful character maps: every character in the first set is swapped for the character at the same position in the second. It is a per-character operation, not a substring one, and that is exactly what separates it from REPLACE. Reach for it when you need to clean up or normalize individual characters -- separators, punctuation, look-alike letters -- in a single pass, without nested calls or regular expressions.

Basic syntax

The signature is simple: TRANSLATE(text, from_set, to_set). Character from_set[i] becomes to_set[i].

SELECT TRANSLATE('abc', 'abc', 'xyz');  -- 'xyz'
SELECT TRANSLATE('a-b-c', 'abc', 'xyz'); -- 'x-y-z'

The dashes stay put: they are not in from_set, so they are untouched. TRANSLATE processes each character independently and in a single pass.

• Matching is strictly positional: first to first, second to second.
• Characters outside from_set are copied verbatim.
• Case matters: a and A are different characters.

Deleting characters: when to_set is shorter

If a character has no partner in the second set, that character is deleted. This is the most practical use of TRANSLATE.

-- Normalize phone numbers: drop separators entirely
SELECT id, TRANSLATE(name, ' .,()-+', '') AS cleaned
FROM users;

Here to_set is empty, so every listed separator simply vanishes. A common case is stripping formatting from phone numbers or SKUs:

-- Strip spaces, dashes and parens from a stored code
SELECT TRANSLATE('+1 (555) 123-45-67', ' ()-+', '');
-- '15551234567'

The same trick tidies order statuses before an export:

SELECT id, TRANSLATE(status, '_', ' ') AS label
FROM orders;

One-pass transliteration

TRANSLATE is excellent for building slug-like values or normalizing characters. Map every "unsafe" character to a safe one in a single call:

-- Map separators and odd punctuation in one shot
SELECT id,
       TRANSLATE(LOWER(name), ' /\\.', '----') AS slug_part
FROM employees;

Read these literals carefully. The from_set is written ' /\\.': inside single quotes PostgreSQL reads \\ as two literal backslash characters, so the set holds five characters -- a space, a forward slash, two backslashes and a dot. The to_set '----' has only four dashes. The space, the forward slash and the backslash each get a dash by position; the second backslash is a repeat and is therefore ignored (see the gotcha below), and the dot is left without a partner and is deleted -- it does not become a dash. If you want the dot kept as a dash, give it a fifth dash in to_set; the set lengths must match your intent exactly, otherwise unpaired characters in from_set are deleted rather than replaced.

Gotcha: characters in from_set should be unique. If a character repeats, only its first occurrence counts. TRANSLATE('a', 'aa', 'xy') yields 'x', not 'y' -- the second a is ignored.

The key difference from REPLACE

REPLACE finds and swaps a whole substring; TRANSLATE works character by character, order-independent.

-- REPLACE swaps a whole substring
SELECT REPLACE('a.b.c', '.', '_');     -- 'a_b_c'

-- TRANSLATE maps single chars; great for sets
SELECT TRANSLATE('a.b,c;d', '.,;', '___'); -- 'a_b_c_d'

To remove three different separators with REPLACE, you need three nested calls; TRANSLATE does it with one set argument.

• Need to swap a word or a multi-character token? That is REPLACE.
• Need to map or delete a set of individual characters? That is TRANSLATE.

Engine notes

• PostgreSQL: full TRANSLATE(text, from, to), deletion on a short to_set works exactly as described above with the dot.
• Oracle: also present, but to_set must not be an empty string (it is treated as NULL and the result becomes NULL). To delete characters, pass a non-empty to_set shorter than from_set.
• MySQL/MariaDB: no TRANSLATE function. Emulate with nested REPLACE or REGEXP_REPLACE.
• ClickHouse: provides translate(s, from, to) and translateUTF8 for correct multibyte handling.

-- ClickHouse: byte-wise vs UTF-8 aware
SELECT translateUTF8(name, 'aeiou', 'AEIOU') FROM users;

The real trap with TRANSLATE is not the swap itself but the edge cases where the sets meet: a from_set longer than to_set (the surplus is deleted), a duplicate in from_set (only the first occurrence counts) and multibyte characters under the byte-wise variant. Before porting slug logic between PostgreSQL, MySQL and ClickHouse, run a tiny table with NULL, an empty string and non-ASCII: engines agree on clean data and diverge precisely on these edges, like the dot in the example above.

Remember: in most engines TRANSLATE works on code points or bytes, so for non-ASCII reach for the UTF-8 aware variant (translateUTF8 in ClickHouse) where one exists.