TRANSLATE
Replaces each character from from with the character at the same position in to.
PostgreSQL 17.9
TRANSLATE(string, from, to)- string
- Source stringtext
- from
- Characters to replacetext
- to
- Replacement characters; characters of from without a pair are removedtext
Return value
A string (text) with the characters replaced
Examples
Replacing characters by position
PostgreSQL 17.9
SELECT TRANSLATE('hello', 'el', 'ip') AS replaced;Only the digits of a phone number
PostgreSQL 17.9
SELECT TRANSLATE('+7 (912) 345-67-89', '()- ', '') AS digits;Vowels in upper case
PostgreSQL 17.9
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.