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