Data cleansing Note: determines whether it is a digital Function

Data cleansing Note: determines whether it is a digital Function

Background]

When data is being processed, a large number of Chinese characters or meaningless English characters appear when inserting data into a field of the number type. Check that all data is junk and need to be cleaned.

 

[Solution]

It is planned to call the number judgment function to complete the judgment. If it is determined to be a number type, it is stored in the data. If it is not a number type, null value is assigned for processing.

Compile the "determine whether it is a number" function as follows:

Createorreplacefunction isnum (v_in varchar2)

Returnvarcharis

V_err exception;

Pragmaexception_init (v_err,-6502 );

Sub_num number;

Begin

Sub_num: = to_number (v_in );

Return v_in;

Exceptionwhen v_errthen

Return '';

End;

This function indicates that if v_in is a number, the number is output. If v_in is not a number, null is returned. Add this function to the data processing statement for determination.

 

[Experiment]

Small knowledge, easy to remember.