Comparison between replace and translate functions in Oracle

Brief comparison:
Replace: String-level replacement
 
For example, select Replace ('acdd', 'cd', 'ef ') from dual; → aefd
 
Translate: character-level replacement
 
For example, select translate ('acdd', 'cd', 'ef ') from dual; → aeff
 
Detailed explanation:
 
Replace:
 
Syntax: Replace (char, search_string [, replacement_string])
 
Replace returns char with every occurrence of search_string replaced with response. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then Char is returned.
 
Explanation: In repalce, each search_string is replaced by replacement_string.
 
Select Replace ('acdd', 'cd', 'ef ') from dual; → aefd
 
If replacement_string is null or null, all search_strings are removed.
 
Select Replace ('acdd', 'cd', '') from dual; → ad
 
If search_string is null, the original char is returned.
 
Select Replace ('acdd', '', 'ef ') from dual; → acdd
 
Select Replace ('acdd', '','') from dual; → acdd (that is, if both are empty)
 
Both search_string and replacement_string, as well as char, can be any of the datatypes char, varchar2, nchar, nvarchar2, clob, or nclob. the string returned is of varchar2 datatype and is in the same character set as char.
 
Explanation: This section specifies the data types that can be used.
 
This function provides functionality related to that provided by the translate function. translate provides single-character, one-to-one substitution. replace lets you substitute one string for another as well as to remove character strings.
 
Explanation: The red part is also the difference between replace and translate.

 

Translate:
Syntax: translate ('Char ', 'From _ string', 'to _ string ')
 
Translate returns char with all records of each character in from_string replaced by its corresponding character in to_string. Characters in Char that are not in from_string are not replaced. The argument from_string can contain in more characters
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 char, then they are removed from the return value. you cannot use an empty string for to_string to remove
All characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns NULL.
 
Explanation: In translate, each character in from_string is replaced by the corresponding character in to_string.
 
Select translate ('acdd', 'cd', 'ef ') from dual; → aeff
 
If from_string is longer than to_string, extra characters in from_string will be removed.
 
Select translate ('acdd', 'acd', 'ef ') from dual; → ef (A is replaced by E, C is replaced by F, and D is removed)
 
Select translate ('acdd', 'cda', 'ef ') from dual; → eff (C is replaced by E, D is replaced by F, and A is removed)
 
If to_string is empty or both are empty, the returned char is also empty. Therefore, to_string cannot be empty.
 
Select translate ('acdd', 'cd', '') from dual; → (null)
 
Select translate ('acdd', '','') from dual; → (null)
 
Practice:
 
How can I determine whether a string is a number?
 
Solution: Convert first: Because to_string cannot be empty, we use # instead.
 
Select translate ('abc123', '#1234567890.', '#') from dual; → ABC
 
# In from_string is replaced by # in to_string, but there is no # character in char. Therefore, this step avoids the rule that to_string must not be empty. Then, the numbers and decimal points are converted to null, so the original string contains only three ABC characters.
 
After the conversion, use nvl2 to determine:
 
Select nvl2 (translate ('abc123', '#1234567890.', '#'), 'string', 'number') from dual; → string
 
The role of nvl2 is: nvl2 (expr1, expr2, expr3)-> expr1 is not null, returns expr2; is null, returns expr3. In this way, we can determine whether a string is a number! Solution!