TRANSLATE

Replace or delete individual characters in a string using a positional character mapping.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

TRANSLATE(str, from, to)

Description

## Overview Performs character-by-character translation on the input string. Each character in the `from` argument is mapped to the character at the same position in the `to` argument. If the `to` argument is shorter than `from`, any source character with no corresponding target is deleted from the output. This makes TRANSLATE an efficient single-pass tool for both substitution and deletion of multiple characters at once. Unlike REPLACE (which operates on whole substrings), TRANSLATE operates on individual characters. For stripping multiple formatting characters in one pass (parentheses, spaces, hyphens from phone numbers; quotes and brackets from free-text fields), TRANSLATE is usually faster and more readable than chained REPLACE calls. ## Behavior - Returns NULL when any argument is NULL. - Each character in `from` maps positionally to the character at the same index in `to`. - When `to` is shorter than `from`, source characters at positions beyond the end of `to` are deleted. - When `to` is longer than `from`, extra characters in `to` are ignored. - Characters not listed in `from` are copied to the output unchanged. - If a character appears more than once in `from`, only the first mapping is used. - Operates on Unicode code points, so multi-byte characters are handled correctly as long as each is a single code point. ## Compatibility - Matches the common SQL TRANSLATE semantics. - Not related to TRANSLATE(expr USING mapping) for charset conversion in some SQL dialects.

Parameters

Name	Type	Description
`str`		Specifies the input string in which per-character substitution occurs.
`from`		Specifies the set of source characters. Each character is matched individually. A character listed here is mapped to the character at the same position in `to`.
`to`		Specifies the replacement characters. If shorter than `from`, any source character without a corresponding target is deleted from the output.

Examples

-- Character substitution
SELECT TRANSLATE('hello', 'el', 'ip');  -- 'hippo'

-- Delete characters by leaving their positions unfilled
SELECT TRANSLATE('hello world', 'lo', 'L');  -- 'heLL wrd'

-- Strip all vowels
SELECT TRANSLATE('hello world', 'aeiou', '');  -- 'hll wrld'

-- Per-character substitution (3-to-3)
SELECT TRANSLATE('abc', 'abc', 'nop');  -- 'nop'

-- Characters not in 'from' are unchanged
SELECT TRANSLATE('hello', 'xyz', 'abc');  -- 'hello'

-- NULL propagates
SELECT TRANSLATE(CAST(NULL AS VARCHAR), 'a', 'b');  -- NULL

Pitfalls

TRANSLATE maps single characters, not substrings. TRANSLATE(s, 'ab', 'cd') replaces every 'a' with 'c' and every 'b' with 'd'; it does not replace the two-character substring 'ab'. Use REPLACE for substring work.
Extra characters in `to` are silently ignored. Out-of-order pairs (where `from` and `to` have different lengths) can produce surprising results.
Duplicate characters in `from` are resolved by the first occurrence; later duplicates are ignored.
TRANSLATE operates on Unicode code points, not grapheme clusters. A base character and its combining accent are two code points; mapping only one of them can produce visually broken output.