Oracle PL/SQL/Char Functions/Translate
Содержание
- 1 all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.
- 2 from_template argument can contain more characters than to_template.
- 3 Shifts the lowercase as well as uppercase characters in a column
- 4 TRANSLATE("555-143-3344", "1234567890","XXXXXXXXXX") phone
- 5 TRANSLATE(): convert numbers
- 6 Translate: replaces a string character by character
- 7 TRANSLATE() returns a string where all occurrences of the from_template are replaced with corresponding characters in the to_template.
- 8 TRANSLATE(x, from_string, to_string): Converts all occurrences of from_string in x to to_string
- 9 Use translate function in update statement
- 10 Using the TRANSLATE() Function: to encode and decode
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>