Database Programming -- Use of translate, -- translate

Database Programming -- Use of translate, -- translate



1. translate syntax
Translate (string, from_str, to_str)
2. explanation: replace each character in the string from_str from left to right with each character corresponding to the string to_str, if there is no matched string, it will be replaced with null. Case Sensitive; to_str cannot be blank;
Example 1: select translate ('Project team 24355translate', '000000', '@ # $') from dual; ------------------------------ Project Team @ $#55 translate ** at this time, @ replaces 2, #3 and $4 are replaced.
If from_str is longer than to_str, no corresponding string will be replaced with null. Example 2: select translate ('Project team 24355translate', '234 Project Team ', '@ # $') from dual; ------------------------------ @ $#55 translate ** the word "Project Team" is deleted in the string because it does not have a corresponding word, or it is replaced with null. If the to_str length is greater than the from_str length, there is no impact and no exception occurs.
Example 3: select translate ('Project team 24355translate', '000000', '@ # $ sdfsdfsdf') from dual; -------------------------- Project Team @ $#55 translate ** results have no impact
Related Applications: 1. determining whether a string is a number or not. Sometimes, when processing a field, you must enter a number in essence. Because the historical data is not properly filled in, as a result, many unqualified junk data with Chinese or English characters are involved. How can we determine whether it is a number? Use replace + translate begin if replace (translate ('Project Team requirements 321789a8 ', '000000 ','@'),'@','') is null then dbms_output.put_line ('this is a number'); else dbms_output.put_line ('this is not a number'); end if; end;
Result: this is not a number.




1. translate syntax
Translate (string, from_str, to_str)
2. explanation: replace each character in the string from_str from left to right with each character corresponding to the string to_str, if there is no matched string, it will be replaced with null. Case Sensitive; to_str cannot be blank;
Example 1: select translate ('Project team 24355translate', '000000', '@ # $') from dual; ------------------------------ Project Team @ $#55 translate ** at this time, @ replaces 2, #3 and $4 are replaced.
If from_str is longer than to_str, no corresponding string will be replaced with null. Example 2: select translate ('Project team 24355translate', '234 Project Team ', '@ # $') from dual; ------------------------------ @ $#55 translate ** the word "Project Team" is deleted in the string because it does not have a corresponding word, or it is replaced with null. If the to_str length is greater than the from_str length, there is no impact and no exception occurs.
Example 3: select translate ('Project team 24355translate', '000000', '@ # $ sdfsdfsdf') from dual; -------------------------- Project Team @ $#55 translate ** results have no impact
Related Applications: 1. determining whether a string is a number or not. Sometimes, when processing a field, you must enter a number in essence. Because the historical data is not properly filled in, as a result, many unqualified junk data with Chinese or English characters are involved. How can we determine whether it is a number? Use replace + translate begin if replace (translate ('Project Team requirements 321789a8 ', '000000 ','@'),'@','') is null then dbms_output.put_line ('this is a number'); else dbms_output.put_line ('this is not a number'); end if; end;
Result: this is not a number.