Extraction of garbled data in the oracle varchar2 Field

The garbled data in the oracle varchar2 field is extracted due to work requirements. During the migration of a batch of data, the varchar2 field of a table should be composed of numbers, but garbled characters appear. For example.

Now we need to extract the garbled data from these hundreds of thousands of data records. Asked down the mother, he told me to use this function: translate the following is the description of this function: http://www.bkjia.com/database/201110/109385.html nonsense not much said the start. 1 create table temp_color_group_new as2 select t. color_group_no, 3 translate (t. color_group_no, '123', '') as new_no, 4 t. svc_inst_id www.2cto.com 5 from temp_color_group t; translate (t. color_group_no, '000000', '') as new_no the color_group_no here is a possible garbled field. '000000': changed based on the actual situation. In this scenario, color_group_no can only be composed of numbers, if there are letters, add them. What if there are Chinese characters? ⊙ B Khan ~~ I did not study it. The person with a high level told me about it .. Learning and learning. '': Here is a space. do not define it as null''. Otherwise, all new numbers after conversion are null .. It can also be defined as a special character such as "_". However, it is a little troublesome to convert it later .. After creating the temporary table temp_color_group_new, trim the space. Update temp_color_group_s set new_no = trim (new_no); at this time, the new_no field must all comply with the numerical rules. If it is not null, It is garbled data or data with special characters, such as line breaks.
 

Author: Beijing-Wang Zhiwei