translate()

A system function for replacing sets of characters with another set

translate() is a system function for replacing sets of characters in a string with another set of characters.

translate() was added in PostgreSQL 6.1.

Usage

translate ( string text, from text, to text ) → text

Each character in the from string will be replaced by the corresponding character in the to string, if presnt in the source string. If a character in the from string does not have a corresponding character in the to string (i.e. the from string is longer than the to string), that character will be removed.

Change history

Examples

Basic execution of translate(), replacing occurences of b and r with p and o respectively:

postgres=# SELECT translate('foobar', 'br', 'po');
 translate 
-----------
 foopao
(1 row)

Characters in the from string with no corresponding character in the to string (in this example: f) will be removed:

postgres=# SELECT translate('foobar', 'brf', 'po');
 translate 
-----------
 oopao
(1 row)

translate() works with multibyte strings:

postgres=# SELECT translate('こん', 'ん', 'の');
 translate 
-----------
 この
(1 row)

Categories

String manipulation, System function

See also

substr()