Oracle PL/SQL/Char Functions/Translate

all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.

 
SQL>
SQL> SELECT
  2       TRANSLATE("4428-2174-5093-1501"
  3                ,"0123456789-"
  4                ,"0123456789") numbers_only
  5  FROM dual;
NUMBERS_ONLY
----------------
4428217450931501

from_template argument can contain more characters than to_template.

 
SQL>
SQL> SELECT
  2       TRANSLATE("4428-2174-5093-1501"
  3                ,"0123456789-"
  4                ,"XXXXXXXXXX*")  hide_num
  5  FROM dual;
HIDE_NUM
-------------------
XXXX*XXXX*XXXX*XXXX

Shifts the lowercase as well as uppercase characters in a column

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DATE  END_DATE        SALARY CITY       DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01   Jason      Martin     1996-JUL-25 2006-JUL-25    1234.56 Toronto    Programmer
02   Alison     Mathews    1976-MAR-21 1986-FEB-21    6661.78 Vancouver  Tester
03   James      Smith      1978-DEC-12 1990-MAR-15    6544.78 Vancouver  Tester
04   Celia      Rice       1982-OCT-24 1999-APR-21    2344.78 Vancouver  Manager
05   Robert     Black      1984-JAN-15 1998-AUG-08    2334.78 Vancouver  Tester
06   Linda      Green      1987-JUL-30 1996-JAN-04    4322.78 New York   Tester
07   David      Larry      1990-DEC-31 1998-FEB-12    7897.78 New York   Manager
08   James      Cat        1996-SEP-17 2002-APR-15    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> --Shifts the lowercase as well as uppercase characters in a column
SQL>
SQL> SELECT id, TRANSLATE(first_name,
  2     "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz",
  3     "EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd")
  4  FROM employee;
ID   TRANSLATE(
---- ----------
01   Newsr
02   Epmwsr
03   Neqiw
04   Gipme
05   Vsfivx
06   Pmrhe
07   Hezmh
08   Neqiw
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>

TRANSLATE("555-143-3344", "1234567890","XXXXXXXXXX") phone

 
SQL>
SQL>
SQL> SELECT TRANSLATE("555-143-3344", "1234567890","XXXXXXXXXX") phone
  2  FROM   dual;
PHONE
------------
XXX-XXX-XXXX




                                                                                                                                      Page           1
SQL>

TRANSLATE(): convert numbers

SQL>
SQL> -- TRANSLATE(): convert numbers
SQL>
SQL> SELECT TRANSLATE(12345,
  2     54321,
  3     67890) FROM dual;
TRANS
-----
09876
SQL>

Translate: replaces a string character by character

SQL> -- Translate: replaces a string character by character.
SQL>
SQL> -- RANSLATE(string, characters_to_find, characters_to_replace_by)
SQL> SELECT TRANSLATE("Mississippi", "s","S") FROM dual;
TRANSLATE("
-----------
MiSSiSSippi

TRANSLATE() returns a string where all occurrences of the from_template are replaced with corresponding characters in the to_template.

 
SQL>
Syntax: TRANSLATE(<string_expression>, <from_template>, <to_template>)
SQL>
SQL>
SQL> SELECT
  2       TRANSLATE ("4428-2174-5093-1501"
  3                 ,"0123456789-"
  4                 ,"XXXXXXXXXX*") hide_num
  5  FROM dual;
HIDE_NUM
-------------------
XXXX*XXXX*XXXX*XXXX

TRANSLATE(x, from_string, to_string): Converts all occurrences of from_string in x to to_string

SQL> -- TRANSLATE(x, from_string, to_string): Converts all occurrences of from_string in x to to_string.
SQL>
SQL> select translate("1111","1","2") from dual;
TRAN
----
2222

Use translate function in update statement

 
SQL>
SQL>  create table nchar_samples(
  2      char_data  char(100),
  3      nchar_data nchar(100)
  4    )
  5    /
Table created.
SQL>
SQL>  insert into nchar_samples( nchar_data )
  2    values ( N"Some text" )
  3    /
1 row created.
SQL>
SQL>  update nchar_samples
  2       set char_data = translate( nchar_data using char_cs )
  3    /
1 row updated.
SQL>
SQL> drop table nchar_samples;
Table dropped.
SQL>
SQL> --

Using the TRANSLATE() Function: to encode and decode

SQL>
SQL> --Using the TRANSLATE() Function
SQL>
SQL> --TRANSLATE(x, from_string, to_string) to convert the occurrences of characters in from_string found in x to corresponding characters in to_string.
SQL>
SQL> --TRANSLATE(): 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
SQL>
SQL> SELECT TRANSLATE("SECRET MESSAGE: MEET ME IN THE PARK",
  2     "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
  3     "EFGHIJKLMNOPQRSTUVWXYZABCD") FROM dual;
TRANSLATE("SECRETMESSAGE:MEETMEINTH
-----------------------------------
WIGVIX QIWWEKI: QIIX QI MR XLI TEVO
SQL>
SQL> --select translate("www.sqle.ru","wjavscom","abced123") from dual;
SQL>
SQL>