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.
<source lang="sql">
SQL> SQL> SELECT
2 TRANSLATE("4428-2174-5093-1501"
3 ,"0123456789-"
4 ,"0123456789") numbers_only
5 FROM dual;
NUMBERS_ONLY
4428217450931501
</source>
from_template argument can contain more characters than to_template.
<source lang="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
</source>
Shifts the lowercase as well as uppercase characters in a column
<source lang="sql">
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>
</source>
TRANSLATE("555-143-3344", "1234567890","XXXXXXXXXX") phone
<source lang="sql">
SQL> SQL> SQL> SELECT TRANSLATE("555-143-3344", "1234567890","XXXXXXXXXX") phone
2 FROM dual;
PHONE
XXX-XXX-XXXX
Page 1
SQL>
</source>
TRANSLATE(): convert numbers
<source lang="sql">
SQL> SQL> -- TRANSLATE(): convert numbers SQL> SQL> SELECT TRANSLATE(12345,
2 54321, 3 67890) FROM dual;
TRANS
09876 SQL>
</source>
Translate: replaces a string character by character
<source lang="sql">
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
</source>
TRANSLATE() returns a string where all occurrences of the from_template are replaced with corresponding characters in the to_template.
<source lang="sql">
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
</source>
TRANSLATE(x, from_string, to_string): Converts all occurrences of from_string in x to to_string
<source lang="sql">
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
</source>
Use translate function in update statement
<source lang="sql">
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> --
</source>
Using the TRANSLATE() Function: to encode and decode
<source lang="sql">
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>
</source>
