Oracle's translate function

I. Syntax: TRANSLATE (string, from_str, to_str) II. Purpose return: replace each character in from_str (all appearing) with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, it is in from_str instead of to_str.

I. Syntax: TRANSLATE (string, from_str, to_str) II. Purpose return: replace each character in from_str (all appearing) with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, it is in from_str instead of to_str.

I. Syntax:
TRANSLATE (string, from_str, to_str)
Ii. Purpose
Replace each character in from_str with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank. Oracle interprets the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL.
3. Permitted locations
Procedural and SQL statements.
Iv. Example
SQL code
1. select translate ('abcdefghj', 'abcdef', '123') FROM dual;
2. TRANSLATE (
3 .--------------
4. ghij 123456
5.
6. select translate ('abcdefghj', 'abcdefghj', '123') FROM dual;
7. TRANSL
8 .----------
9. 123456
Syntax: TRANSLATE (expr, from,)
Expr: represents a string of characters. from and to correspond one to one from left to right. If not, it is considered as a null value.
Example:
Select translate ('abcbbaadef ', 'ba',' # @ ') from dual (B will be replaced by #, and a will be replaced)
Select translate ('abcbbaadef ', 'bad',' # @ ') from dual (B will be replaced by #, a will be replaced by @, and d's value is null, will be removed)
Therefore, the results are as follows: @ # c ###@ def and @ # c ##@ ef.
Syntax: TRANSLATE (expr, from,)
Expr: represents a string of characters. from and to correspond one to one from left to right. If not, it is considered as a null value.
Example:
Select translate ('abcbbaadef ', 'ba',' # @ ') from dual (B will be replaced by #, and a will be replaced)
Select translate ('abcbbaadef ', 'bad',' # @ ') from dual (B will be replaced by #, a will be replaced by @, and d's value is null, will be removed)
Therefore, the results are as follows: @ # c ###@ def and @ # c ##@ ef.

Example:

Example 1: convert a number to 9, convert other uppercase letters to X, and then return.

Select translate ('2krw229', '0123456789abcdefghijklmnopqrstuvwxyz ', '999999999999xxxxxxxxxxxxxxxxxxxxxxxxxxxx') "License" FROM DUAL

Example 2: retain the number and remove other uppercase letters.

Select translate ('2krw229', '0123456789abcdefghijklmnopqrstuvwxyz ', '123') "Translate example" FROM DUAL

Luo Yong's supplementary example is as follows:

Example 3: The example shows that it is processed by character rather than byte. If to_string has more characters than from_string, the extra characters seem useless, and does not cause exceptions.

Select translate ('I am Chinese, I Love China', 'China') "Translate example"

FROM DUAL

Example 4: The following example shows that if the from_string Character Count is greater than to_string, the extra characters will be removed, that is, the ina three characters will be removed from the char parameter, of course it is case sensitive.

Select translate ('I am Chinese, I love China', 'China', 'China') "Translate example"

FROM DUAL

Example 5: The following example shows that if the second parameter is a null string, the entire return value is null.

Select translate ('2krw229 ',
'0123456789abcdefghijklmnopqrstuvwxy ',
'')" License"
FROM DUAL

Example 6: When transferring funds at a bank, you often see that the account owner only displays the last word of the name, and the rest are replaced by asterisks. I will use translate to make something similar.

Select translate ('China ',

Substr ('China', 1, length ('China')-1 ),

Rpad ('*', length ('China'), '*') "License"

FROM DUAL