Recent data processing often found that there are many characters in the number, the execution of the process reported "Ora-01722:invalid number" exception, organized a bit, can be removed by the following methods:
1, replace use simple, the wording is more complex, can only handle known characters
With Test_table1 as (select 1 Seq_num, ' 2134?654?ag d35 ' strings from dual Union all Select 2 Seq_num, ' 651 354a g5 dd21 ' strings from dual) Select Seq_num, replace (replace (replace (replace (strings, '? ', '), ' a ', '), ' G ', '), ' d ', '), ', ') from Test_table1; ---execution Results 1 213465435 2 651354521
2, translate use simple, simple, can only handle known characters, strings, to find characters, substitution characters, can not be null, otherwise return NULL, the string is replaced by the search order, if none is removed
With Test_table1 as (select 1 Seq_num, ' 2134?654?ag d35 ' strings from dual Union all Select 2 seq_num, ' 651 354a g5 dd21 ' strings from dual) Select Seq_num, translate (strings, ' 1asdfasg. ', ' 1 ') from Test_table1; ---execution Results 1 213465435 2 651354521
3. regexp_replace regular expression Enhanced replace parameter, can handle all characters according to regular pattern
With test_table1 As (select 1 seq_num, ' 2134?654?ag d35 ' Strings from dual union all select 2 seq_num, ' 651 354a g5 dd ' strings from dual union all select 3 seq_num, ' 2134654?ag d35 ' strings from dual union all select 4 seq_num, ' 16?54?aasdgf78as ' Strings from dual union all select 5 seq_num, ' [email protected]#$%^&am p;* () ~: "+_?><|~8as ' strings from dual) select seq_num, regexp_replace ( strings, ' [^0-9] ', ') From test_table1; ---execution results 1 213465435 2 651354521 3 213465435 4 165478 5 168
Note: These are previously written and are now transferred to OSC, blog original: http://www.cnblogs.com/godsweet/p/3274947.html
Special character processing in Oracle data