TRANSLATE
Replaces each character from from with the character at the same position in to.
PostgreSQL 17.5
TRANSLATE(string, from, to)Parameters
- stringtext
- Source string
- fromtext
- Characters to replace
- totext
- Replacement characters; characters of from without a pair are removed
Return value
A string (text) with the characters replaced
Examples
Replacing characters by position
PostgreSQL 17.5
SELECT TRANSLATE('hello', 'el', 'ip') AS replaced;Only the digits of a phone number
PostgreSQL 17.5
SELECT TRANSLATE('+7 (912) 345-67-89', '()- ', '') AS digits;Vowels in upper case
PostgreSQL 17.5
SELECT member_name,
TRANSLATE(member_name, 'aeiou', 'AEIOU') AS vowels_up
FROM FamilyMembers;Details
Unlike REPLACE, the function works with single characters, not with a substring: TRANSLATE('hello', 'el', 'ip') replaces e with i and l with p. If to is shorter than from, the extra characters of from are removed, so with an empty to the function simply deletes characters.