How to understand oracle translate ()
This article introduces you how to understand oracle translate (), the content is very detailed, interested friends can refer to, hope to be helpful to you.
First, grammar:
TRANSLATE (string,from_str,to_str)
II. Purpose
Returns the string after replacing each character in the (all occurrences) from_str with the corresponding character in the to_str. TRANSLATE is a superset of the functionality provided by REPLACE. If from_str is longer than to_str, extra characters in from_str rather than to_str will be removed from string because they do not have corresponding replacement characters. To_str cannot be empty. Oracle interprets the empty string as NULL, and if any parameter in TRANSLATE is NULL, the result is also NULL.
III. Allowable locations
Procedural statements and SQL statements.
IV. Examples
Sql code
1. SELECT TRANSLATE ('abcdefghij','abcdef','123456') FROM dual
2. TRANSLATE (
3.-
4. 123456ghij
5.
6. SELECT TRANSLATE ('abcdefghij','abcdefghij','123456') FROM dual
7. TRANSL
8.-
9. 123456
Syntax: TRANSLATE (expr,from,to)
Expr: represents a string of characters. From and to have an one-to-one correspondence from left to right. If they do not correspond, they will be regarded as null.
For example:
Select translate ('abcbbaadef','ba','#@') from dual (b will be replaced by #, a will be replaced by @)
Select translate ('abcbbaadef','bad','#@') from dual (b will be replaced by #, a will be replaced by @, and the value corresponding to d will be null and will be removed)
Therefore: the results are @ # c##@@def and @ # c##@@ef in turn
Syntax: TRANSLATE (expr,from,to)
Expr: represents a string of characters. From and to have an one-to-one correspondence from left to right. If they do not correspond, they will be regarded as null.
For example:
Select translate ('abcbbaadef','ba','#@') from dual (b will be replaced by #, a will be replaced by @)
Select translate ('abcbbaadef','bad','#@') from dual (b will be replaced by #, a will be replaced by @, and the value corresponding to d will be null and will be removed)
Therefore: the results are @ # c##@@def and @ # c##@@ef in turn
Examples are as follows:
Example 1: convert the number to 9, other uppercase letters to X, and then return.
SELECT TRANSLATE ('2KRW229'
'0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ'
'999999999999XXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL
Example 2: keep the numbers and remove other uppercase letters.
SELECT TRANSLATE ('2KRW229'
'0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ'
'0123456789') "Translate example"
FROM DUAL
Example 3: the example proves that it is processed according to characters, not bytes. If the number of characters in to_string is more than that in from_string, the extra characters seem to be of no use and will not throw an exception.
SELECT TRANSLATE ('I am Chinese, I love China', 'China', 'China') "Translate
Example "
FROM DUAL
Example 4: the following example proves that if the number of characters in from_string is greater than to_string, then the extra characters will be removed, that is, the three characters of ina will be removed from the char parameter, of course, case-sensitive.
SELECT TRANSLATE ('I am Chinese, I love China', 'China',' China') "Translate
Example "
FROM DUAL
Example 5: the following example proves that if the second parameter is an empty string, the entire null is returned.
SELECT TRANSLATE ('2KRW229'
'0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ'
'') "License"
FROM DUAL
Example 6: when transferring money to a bank, I often see that the account holder only shows the last word of his name, and the rest is replaced by an asterisk. I will use translate to do something similar.
SELECT TRANSLATE ('Chinese'
Substr ('Chinese', 1 'Chinese')-1
Rpad ('*', length ('Chinese'),'*') "License"
FROM DUAL
On how to understand oracle translate () to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
