1.translate syntax
Translate (STRING,FROM_STR,TO_STR)
2. Explanation: Each character of the string from_str that exists in a string string is replaced from left to right with each character of the string to_str, and if there is no corresponding, it is replaced with NULL in the string. case-sensitive; to_str cannot be empty;
Example 1:select translate (' project group 24355translate ', ' 234 ', ' @#$ ') from dual; ------------------------------Project group @$ #55translate * * At this time equals @ replaced 2, #替换了3, $ replaced 4
If the length of the FROM_STR is greater than the length of to_str, then there is no corresponding on the string that will be replaced with an empty example 2:select translate (' project group 24355translate ', ' 234 project group ', ' @#$ ') from dual ; ------------------------------ @$ #55translate * * "project group" three words were deleted in the string because there was no correspondence, or were replaced with an empty one. If the length of the TO_STR is greater than the length of from_str, there is no effect and no exception occurs
Example 3:select translate (' project group 24355translate ', ' 234 ', ' @# $sdfsdfsdf ') from dual; ------------------------------Project group @$ #55translate * * results have no effect
Related applications: 1. Determine whether a string is all numbers sometimes in the processing of a field, the essence of the requirement to fill in the numbers, because the historical data filled with inadequate specifications, resulting in a lot of Chinese or English unqualified garbage data, how to determine whether it is a number? Use replace+translate begin if replace (Translate (' project group requires 321789a8 ', ' 0123456789 ', ' @ '), ' @ ', ') 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: Each character of the string from_str that exists in a string string is replaced from left to right with each character of the string to_str, and if there is no corresponding, it is replaced with NULL in the string. case-sensitive; to_str cannot be empty;
Example 1:select translate (' project group 24355translate ', ' 234 ', ' @#$ ') from dual; ------------------------------Project group @$ #55translate * * At this time equals @ replaced 2, #替换了3, $ replaced 4
If the length of the FROM_STR is greater than the length of to_str, then there is no corresponding on the string that will be replaced with an empty example 2:select translate (' project group 24355translate ', ' 234 project group ', ' @#$ ') from dual ; ------------------------------ @$ #55translate * * "project group" three words were deleted in the string because there was no correspondence, or were replaced with an empty one. If the length of the TO_STR is greater than the length of from_str, there is no effect and no exception occurs
Example 3:select translate (' project group 24355translate ', ' 234 ', ' @# $sdfsdfsdf ') from dual; ------------------------------Project group @$ #55translate * * results have no effect
Related applications: 1. Determine whether a string is all numbers sometimes in the processing of a field, the essence of the requirement to fill in the numbers, because the historical data filled with inadequate specifications, resulting in a lot of Chinese or English unqualified garbage data, how to determine whether it is a number? Use replace+translate begin if replace (Translate (' project group requires 321789a8 ', ' 0123456789 ', ' @ '), ' @ ', ') 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.
The use of database programming--translate
