The use of Oracle translate functions, and the difference from replace

Translate is a function to replace a character

Grammar:

Translate (char, FROM_STR,TO_STR)

where char is the string to be processed.

From_str is a set of character sets to be replaced in order, note that the character set is not a string.

To_str is the character set that corresponds from_str to be replaced in order.

For example:

Translate (' ABCD ', ' ab ', ' ab ')

After executing the above statement, ' a ' in ' ABCD ' is replaced by ' a ', ' B ' is replaced by ' B '. So back to "ABCD"

This looks very similar to the Replace function.

However, in

Replace (' ABCD ', ' ab ', ' ab ') statement, is the ' ABCD ' in the ' ab ' whole string replaced with ' AB ', you should be able to see the difference between the above translate.

As an example,

Translate (' ACBD ', ' ab ', ' AB ') will return "ACBD" because ' a ' is replaced by ' a ', ' B ' is replaced by ' B ', and the substitution is handled separately for every 1 characters.

The replace (' ACBD ', ' ab ', ' ab ') returns "ACBD" because the string ' ab ' cannot be found, so there is no replacement processing.

Here are some translate features to note.

1.

In translate (' ABCD ', ' abc ', ' A '), the character A is replaced by ' a ', and B and C are not specified for replacement, and Oracle defaults to NULL. So return to ' Ad '.

2.

Translate (' ABCD ', ' abc ', '), even a replaced character is omitted, is it because the ABC 3 characters are replaced by NULL, so return d?

The answer is wrong, and if To_str is blank (') or null, all characters will be replaced with NULL, so the expression returns NULL.

The 3.translate has many special uses,

For example, if I want to remove all the numbers in the 1 string, you can use the
 

translate (str, ' a1234567890 ', ' a ')

In this case, a (if any) of the string will be replaced with a, while the other numbers will be substituted for NULL, note that it is null instead of a space.

Or

translate (str, ' 1234567890 ', ')

Use a space to replace the above ' a ' also OK, note that from_str and to_str are beginning with a space.

And not with

translate (str, ' 1234567890 ', ') , refer to the above features 2 , NULL is returned of the AH.