The TRANSLATE Function

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The TRANSLATE Function

Post by JimKnicely » Fri Aug 10, 2012 5:41 pm

The TRANSLATE function replaces individual characters in a string with characters you specify on a character by character basis.

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)
Using the TRANSLATE function we can create our own super secrete decoders:

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)
Remember the classic film 2001: A Space Odyssey and that crazy H.A.L. 9000 intelligent computer? Ever wonder how the computer was named? Well, its name was derived from a simple translation of a well know acronym at the time …

Code: Select all

dbadmin=> SELECT translate('I.B.M.', '.ABCDEFGHIJKLMNOPQRSTUVWXYZ', '.ZABCDEFGHIJKLMNOPQRSTUVWXY') shift_chars_left;
shift_chars_left
------------------
H.A.L.
(1 row)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”