The difference between translate and replace in SQL

  1.translate Syntax: Translate (char, from, to) Usage: Returns a string after each character appearing in from is replaced with the corresponding character in to. If the from is longer than the to string, then more characters in from than to will be deleted. One of the three parameters is empty, and the return value will also be a null value. For example:sql> Select Translate (' ABCDEFGA ', ' abc ', ' Wo ') returns the value from dual; The return value-------WODEFGW analysis: The statement converts ' abc ' in ' Abcdefga ' to ' wo ', so that the ' a ' in ' ABC ' corresponds to ' w ' in ' wo ', so that all ' a ' in ' ABCDEFGA ' is converted to ' W '; and ' B ' in ' ABC ' corresponds to ' o ' in ' wo ', so the ' B ' in ' ABCDEFGA ' is converted to ' o '; ' C ' in ' ABC ' has no corresponding character in ' Wo ', so the ' C ' in ' ABCDEFGA ' is deleted; Simply put, the character from is converted to the position in to, and the character in the return value will be deleted if the corresponding character is not found in the to. In the actual business, can be used to delete some of the exception data, such as a field in table a t_no the phone number, and the phone number itself should be a number of strings, in order to remove those with non-numeric exception data, the use of the Translate function: sql> Delete From a, where length (translate (a.t_no), ' 0123456789 ' | | | a.t_no, ' 0123456789 ')) <> length (Trim (a.t_no)); 2.replace Syntax: replace (char, search_string,replacement_string) Usage: Converts the string search_string in char to a string replacement_string. For example:sql> select REPLACE (' Fgsgswsgs ', ' FK ', ' J ') returns the value from dual; The return value---------fgsgswsgs sql> Select REPLACE (' fgsgswsgs ', ' SG ', ' EEERRRTTT ') returns the value from dual; return value-----------------------Fgeeerrrtttsweeerrrttts Analysis: In the first example, because there is no string matching the ' FK ' in the ' fgsgswsgs ', the return value is still ' fgsgswsgs '; The second example converts the string ' sg ' in ' fgsgswsgs ' to ' eeerrrttt '. Summary: To sum up, replace and translate are alternate functions,

Only replace is for a string, and translate is for a single character.