Oracle uses the TRANSLATE function to replace characters in the string

Oracle uses the TRANSLATE function to replace one character in a string. How can I replace (12345, 1234, 56789) with '192*12345 '? First, replace: ChenZw> select replace ('(12345 )',',','*'),'(', ''''), ')', ''') from dual; REPLACE (RE ---------------- '2017*1234*56789 'has selected 1 line. The above code is obviously rude. Let's take a look at the official introduction of the Replace function: Purpose REPLACE returns char with every occurrence of search_string replaced with replacement_string. if replacement_string is omitted or null, then all occurrences ofsearch_string are removed. if search_string is null, then char is returned. both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, C LOB, or NCLOB. the string returned is in the same character set as char. the function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB. examples The following example replaces occurrences of J with BL: select replace ('Jack and JUE ', 'J', 'bl') "Changes" from dual; changes -------------- BLACK and BLUE method 2: Use the translate function: ChenZw> select translate ('(12 34,56789, 12345) ',' (,) ', ''' * ''') as str from dual; STR ---------------- '2017*1234*56789' selected 1 row. Using this function to solve this problem seems more comfortable, at least not as violent as replace. Let's take a look at the official introduction to the translate function: purpose TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. characters in expr that are not in from_string are not replaced. the argument from_string can contain in more characters than to_string. in this case, the extra characters at the end offrom_string have no corresponding characters in to_string. if these extra characters appear in expr, then they are removed from the return value. if a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used. you cannot use an empty string for to_string to remove all characters in from_string from the return value. oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null. to remove all characters in from_string, concatenate another character to the beginning offrom_string and specify this character as the to_string. for example, TRANSLATE (expr, 'x0123456789 ', 'x') removes all digits from expr. TRANSLATE provides functionality related to that provided by the REPLACE function. REPLACE lets you substitute a single string for another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation. this function does not support CLOB data directly. however, CLOBs can be passed in as arguments through implicit data conversion. examples The following statement translates a book title into a string that cocould be used (for example) as a filename. the from_string contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character ). the to_string contains only three underscores. this leaves the fourth character in the from_string without a corresponding replacement, so apostrophes are dropped from the returned value. select translate ('SQL * Plus User's Guide',' */''', '___') from dual; TRANSLATE ('SQL * PLUSU--------------------SQL _ Plus_Users_Guide