SYNTAX: TRANSLATE ( string_to_replace , from_string , to_string );
The string_to_replace parameter contains the string to be translated, the from_string parameter lists the characters to be replaced, and the to_string parameter lists the corresponding characters to replace the characters listed the from_string parameter (by position).
Examples:
Code: Select all
dbadmin=> SELECT translate ('abc', 'a', 'x'),
dbadmin-> translate ('abc', 'ab', 'xy'),
dbadmin-> translate ('abc', 'ba', 'yx'),
dbadmin-> translate ('abc', 'abc', 'xyz'),
dbadmin-> translate ('abc', 'cba', 'zyx'),
dbadmin-> translate ('abc', 'ac', 'xz'),
dbadmin-> translate ('abc', 'ca', 'zx');
translate | translate | translate | translate | translate | translate | translate
-----------+-----------+-----------+-----------+-----------+-----------+-----------
xbc | xyc | xyc | xyz | xyz | xbz | xbz
(1 row)
Code: Select all
dbadmin=> SELECT translate('qwp1oe1ri1utyaslk1djh9',
dbadmin(> 'lit9wqdakureophjys1', 'mnw!iJDseaasimABeo ') msg_will_self_destruct;
msg_will_self_destruct
------------------------
Jim is an awesome DBA!
(1 row)
Code: Select all
dbadmin=> SELECT translate('I.B.M.', '.ABCDEFGHIJKLMNOPQRSTUVWXYZ', '.ZABCDEFGHIJKLMNOPQRSTUVWXY') shift_chars_left;
shift_chars_left
------------------
H.A.L.
(1 row)