oracle-function-translate

--translate function
Function Description:
Translate (str, FROM_STR, TO_STR), replacing the from_str in str with TO_STR.
Translate is a superset of the functionality provided by replace. From_str and To_str are the corresponding relationships from left to right, and if they cannot correspond, they are treated as null values.
If FROM_STR is longer than to_str, then characters in from_str that are not in TO_STR are removed from STR because they do not have the corresponding substitution characters.
TO_STR cannot be null, Oracle interprets an empty string as null, and if any argument in translate is null, the result is also null.
Example Description 1:
SELECT TRANSLATE (' Abcdefghij ', ' abcdef ', ' 123456 ') from dual; --Return 123456ghij
SELECT TRANSLATE (' Abcdefghij ', ' abcdefghij ', ' 123456 ') from dual; --Return 123456
SELECT TRANSLATE (' 00abcf00 ', ' abc ', ' 123 ') newstr from dual; --Return to 00123F00
SELECT TRANSLATE (' abcbbaadef ', ' ba ', ' #@ ') from dual-(b will be replaced by #, a will be replaced by @) to return @ #c ##@ @def
SELECT TRANSLATE (' abcbbaadef ', ' bad ', ' #@ ') from dual-(b will be replaced by #, a will be replaced by @, D corresponds to a null value, will be moved) return @ #c ##@ @ef
Example Description 2:
1. Convert number to 9, other uppercase letters to X
Select TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX ') " License "from DUAL; --Return to 9xxx999
2. Keep the numbers and remove the other capitals.
Select TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 0123456789 ') "TRANSLATE example" from DUAL; --Return 2229
3, the proof is processed according to the character, is not according to the byte processing, if To_string's character number is more than the from_string, the extra number of characters does not seem useful, also does not throw the exception.
Select TRANSLATE (' I am Chinese, I love China ', ' Chinese ', ' China ') ' TRANSLATE example ' from DUAL; --Return I am ch people, I love CH
4, prove that if the number of characters from_string is greater than to_string, then the extra characters will be removed, that is, ina three characters will be removed from the char parameter, of course, case-sensitive.
Select TRANSLATE (' I am Chinese, I love Chinese ', ' China ', ' PRC ') ' TRANSLATE example ' from DUAL; --Return i m China ese, I love China
5, proves that if the second argument is an empty string, the entire return is null.
Select TRANSLATE (' I am Chinese, I love China ', ' China ', NULL) ' TRANSLATE example ' from DUAL; --Return null value
6, in the bank transfer often see the account person only display the last word of the name, the rest with asterisks instead, I will use translate to do something similar.
Select TRANSLATE (' Chinese ', substr (' Chinese ', 1,length (' Chinese ')-1), Rpad (' * ', Length (' Chinese '), ' * ')) "License" from DUAL; --Return to * * person

oracle-function-translate