Replacement functions in Oracle Replace and translate functions

1. TRANSLATE syntax: TRANSLATE (Char, from, tousage: Returns a string that replaces each character that appears in the From, with the corresponding character in to.            If the from is longer than the to string, then the characters that are more than to in the from will be deleted. One of the three parameters is empty, and the return value will also be a null value. Example: SQL> SelectTranslate'ABCDEFGA','ABC','wo') return value fromdual; return value-------WODEFGW Analysis: This statement will be'ABCDEFGA'In the'ABC'Converted to'wo', since'ABC'In'a'Corresponding'wo'In the'W', it will'ABCDEFGA'In the'a'Convert all into'W'; and'ABC'In'b'Corresponding'wo'In the'o', it will'ABCDEFGA'In the'b'Convert all into'o'; 'ABC'In the'C'In'wo'does not correspond to the characters in the'ABCDEFGA'In the'C'Delete all;            Simply put, the character in the from is converted to the character corresponding to the position in to, and if the corresponding character is not found in to, the character in the return value is deleted.            In the actual business, can be used to delete some of the exception data, such as Table A field t_no represents a telephone number, and the phone number itself should be a string consisting of numbers, in order to delete those containing non-numeric exception data, I used the Translate function: SQL> Delete  fromA,whereLength (Translate (Trim (a.t_no),'0123456789' ||A.t_no,'0123456789'))<>Length (Trim (a.t_no));2.ReplaceSyntax:REPLACE(Char, search_string,replacement_string) Usage: Converts all string search_string in char to string replacement_string. Example: SQL> Select REPLACE('fgsgswsgs','FK','J') return value fromdual; return value---------fgsgswsgs SQL> Select REPLACE('fgsgswsgs','SG','EEERRRTTT') return value fromdual; return value-----------------------Fgeeerrrtttsweeerrrttts Analysis: In the first example, as a result of'fgsgswsgs'does not match the'FK'matches the string, so the return value is still'fgsgswsgs'; The second example will'fgsgswsgs'The string in'SG'Convert All to'EEERRRTTT'. Summary: To sum up, replace and translate are alternative functions, except that replace is for a string, and translate is for a single character. 

Transferred from: https://www.cnblogs.com/xiaoqisfzh/p/5620890.html

Replacement functions in Oracle Replace and translate functions