grammar:
translate(expr, from_strimg, to_string)
Introduction:
translate returns expr, where from_stringEach characterAll occurrences ofCorresponding charactersreplace. Characters not in from_string in expr will not be replaced. If expr is a string, then you have to put it in single quotes. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in the characters, they will be removed from the return value.
You cannot use an empty string of to_string to remove all characters in from the return value. Oracle database interprets an empty string as empty, and returns null if this function has an empty parameter.
translate provides functions related to the repeat function. replace lets you replace another string with one string and delete the string. translate allows you to perform multiple single-character, one-to-one replacements in one operation.
This function does not directly support CLOB data. However, CLOB can be passed as a parameter through implicit data conversion.
example:
The following statement converts a sentence into a string separated by underscores. from_string contains four characters: pound sign, dollar sign, space, asterisk. to_string contains only one @ symbol and two underscores. This makes the fourth character in from_string have no corresponding replacement, so the asterisk is removed from the returned value.
SELECT TRANSLATE('itmyhome#$is my* email', '#$ *', '@__') from dual
[email protected]_is_my_emailThe difference between replace function
select translate('itmyhome#163%com', '#%', '@.') from dual;
select replace('itmyhome#163%com', '#%', '@.') from dual;
itmyhome@
itmyhome#163%comThe translate function above replaces # with @ and % with .
However, replace did not achieve this effect because no combination of #% was found
This is the end of this article about the usage of translate functions in Oracle. For more information about the usage of Oracle translate functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!