Special character processing in Oracle data

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