Using the TRANSLATE() Function

You use TRANSLATE( x , from_string , to_string ) to convert the occurrences of characters in from_string found in x to corresponding characters in to_string . The easiest way to see how TRANSLATE() works is to examine some examples.

The following example uses TRANSLATE() to shift each character in the string SECRET MESSAGE: MEET ME IN THE PARK by four places to the right: A becomes E, B becomes F, and so on:

  SELECT TRANSLATE('SECRET MESSAGE: MEET ME IN THE PARK',     'ABCDEFGHIJKLMNOPQRSTUVWXYZ',     'EFGHIJKLMNOPQRSTUVWXYZABCD') FROM dual;  TRANSLATE('SECRETMESSAGE:MEETMEINTH ----------------------------------- WIGVIX QIWWEKI: QIIX QI MR XLI TEVO 

The next example takes the output of the previous example and shifts the characters four places to the left: E becomes A, F becomes B, and so on:

  SELECT TRANSLATE('WIGVIX QIWWEKI: QIIX QI MR XLI TEVO',     'EFGHIJKLMNOPQRSTUVWXYZABCD',     'ABCDEFGHIJKLMNOPQRSTUVWXYZ') FROM dual;  TRANSLATE('WIGVIXQIWWEKI:QIIXQIMRXL ----------------------------------- SECRET MESSAGE: MEET ME IN THE PARK 

You can of course pass column values to TRANSLATE() . The following example passes the name column from the products table to TRANSLATE() and also shifts the lowercase as well as uppercase characters:

  SELECT product_id, TRANSLATE(name,     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',     'EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd')   FROM products;  PRODUCT_ID TRANSLATE(NAME,'ABCDEFGHIJKLMN ---------- ------------------------------  1 Qshivr Wgmirgi  2 Gliqmwxvc  3 Wytivrsze  4 Xero Aev  5 D Jmpiw  6 2412: Xli Vixyvr  7 Wtegi Jsvgi 9  8 Jvsq Ersxliv Tperix  9 Gpewwmgep Qywmg  10 Tst 3  11 Gviexmzi Cipp  12 Qc Jvsrx Pmri 

You can also use TRANSLATE() to convert numbers . The following example takes the number 12345 and converts 5 to 6, 4 to 7, 3 to 8, 2 to 9, and 1 to 0:

  SELECT TRANSLATE(12345,     54321,     67890) FROM dual;  TRANS ----- 09876