Deep understanding of Oracle String Functions translate ()

TRANSLATE is a superset of the functionality provided by REPLACE. 

Syntax: TRANSLATE (STRING,FROM_STR,TO_STR) parameter

1:string, original string parameter

2:from_str, string parameter 

3:to_str that needs to be replaced, used to replace the FROM_STR string Rule

Description: String: Represents a string of characters, From_str and to_str are the corresponding relationships from left to right, and if they cannot correspond, they are considered null values

Objective returns a string that replaces each character in the FROM_STR (all occurrences) with the corresponding character in To_str.

The translate function is replaced by the following rules: 

First, the function is replaced with a character, not as a string (replace method). 

Second, in the From_str and to_str two strings, the two characters in the same position form a mapping relationship. 

At this point, three kinds of cases

1 from_str characters More, then the more parts do not map, equivalent to delete 

2 from_str characters are the same, and to_str exactly form one by one corresponding to the relationship 

3 From_str characters, then the length of From_str map, to_ The extra part of STR doesn't matter.

Special case: In the map, a character in the from_str, mapped to more than one character in To_str, whichever is the first. 

Examples are as follows: 

1. From_str= ' BCD ', to_str= ' mn ' mapping relationship: B-->m c-->n D does not map, and when replaced, the D character in string is deleted. Equivalent to deleting 

2. from_str= ' BC ', to_str= ' mn ' mapping Relationship: B-->m c-->n 

3. from_str= ' BC ', to_str= ' mndd ' mapping relationship: b-->m c-->n 

4. From_str= ' bcc ', to_str= ' mndd ' mapping relationship: B-->m c-->n c-->d at this time, found C has two mappings, which is the first, that is, C-->n Finally, the replacement process is, Replaces all characters in a string that match the first character in From_str, and then does not replace the character that matches the second character in the From_str, and so on.

Note: Oracle interprets an empty string as null, and if any argument in translate is null, the result is null
Third, the permitted use of positional procedural statements and SQL statements.

Example SQL code 

1. When a parameter is NULL, the result is null 

Select translate (' abcdef ', ' CE ', null) from dual;--result: null Select translate (' ABCdef ', NULL, ' CE ') from dual;--result: null Select translate (NULL, ' CE ', ' abcdef ') from dual;--result: null

2. Parameters are not NULL 

Select translate (' Abcbbaadef ', ' ba ', ' #@ ') from dual (b will be replaced by #, a will be @ substituted) Select translate (' Abcbbaadef ', ' bad ', ') @ ') from dual (b will be replaced by #, a will be replaced by @, D corresponds to a null value, will be removed) Therefore: The result is: @ #c ##@ @def and @ #c ##@ @ef

3. Application 

Example One: Converts the number to 9, the other uppercase letters to x, and returns.                                               

SELECT TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX ') "License" from DUAL

Example two: Keep numbers and remove other uppercase letters.

Select TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ' 0123456789 ') "TRANSLATE example" from DUAL

Example three: the example proves to be processed by character, not by Byte, and if the number of characters in the to_string is more than from_string, the number of characters that appear is less useful and does not throw an exception. 

Select TRANSLATE (' I am Chinese, I love China ', ' Chinese ', ' China ') ' TRANSLATE example ' from DUAL

Example four: The following example shows that if the number of characters in the from_string is greater than to_string, the extra characters are removed, that is, the INA three character is removed from the char parameter, and of course it is case-sensitive. 

Select TRANSLATE (' I am Chinese, I love country ', ' China ', ' Chinese ') ' TRANSLATE example ' from DUAL

Example five: The following example proves that if the second argument is an empty string, the entire return null. 

Select TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', ') ' License ' from DUAL

Example six: When the bank transfers often see the account person only displays the last word of the name, the rest with the asterisk instead, I use translate to do a similar thing. 

Select TRANSLATE (' Chinese ', substr (' Chinese ', 1,length (' Chinese ')-1), Rpad (' * ', Length (' Chinese '), ' * ') "License" from DUAL