Differences between translate and replace in SQL

1. translate Syntax: TRANSLATE (char, from, to) usage: returns a string that replaces each character that appears in from with the corresponding character in. If from is longer than to string, the extra characters in from are deleted. One of the three parameters is null, And the return value is also null. Example: SQLselecttransla

1. translate Syntax: TRANSLATE (char, from, to) usage: returns a string that replaces each character that appears in from with the corresponding character in. If from is longer than to string, the extra characters in from are deleted. One of the three parameters is null, And the return value is also null. Example: SQL select transla

1. translate Syntax: TRANSLATE (char, from, to) usage: returns a string that replaces each character that appears in from with the corresponding character in. If from is longer than to string, the extra characters in from are deleted. One of the three parameters is null, And the return value is also null. Example: SQL> select translate ('abcdefga ', 'abc', 'wo') return value from dual; Return Value ------- wodefgw analysis: this statement converts 'abc' in 'abcdefga 'to 'wo'. Because 'A' in 'abc' corresponds to 'w' in 'Wo ', therefore, 'A' in 'abcdefga 'is converted to 'w', while' B 'in 'abc' corresponds to 'O' in 'Wo ', therefore, 'B' in 'abcdefga 'is converted to 'O'. 'C' in 'abc' does not have the corresponding character in 'Wo, therefore, you can delete all 'C' in 'abcdefga '. Simply put, you can convert the character from to the character corresponding to its position, if the corresponding character cannot be found in to, the character in the returned value will be deleted. In actual business, it can be used to delete some abnormal data. For example, the t_no field in Table a indicates the phone number, and the phone number itself should be a string composed of numbers, to delete abnormal data that contains non-numbers, the translate function is used: SQL> delete from a, where length (translate (trim (. t_no), '000000' |. t_no, '200') <> length (trim (. t_no); 2. replace Syntax: 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 from dual; Return Value --------- fgsgswsgs SQL> select REPLACE ('fgsgswsgs ', 'sg ', 'eeerrrttt') return value from dual; Return Value ------------------- analyze: In the first example, because 'fgsgswsgs 'does not have a string that matches 'fk', the return value is still 'fgsgswsgs '; in the second example, convert all the strings 'sg 'in 'fgsgswsgs' to 'eeerrrttt '. Conclusion: replace and translate are both substitution functions,

However, replace is for strings, while translate is for a single character.