Oracle determines whether it is a numeric or numeric string (sqlserver isnumeric)

 

Requirements:

If the field value is not a numeric or numeric string, update the field value''

1. Function Method (batch release is not allowed ):

create   or   replace   function   isNumber(p_in   varchar2)   return   boolean   as         i   number; begin         i:=to_number(p_in);         return   true; exception               when   others   then                 return   false; end   ; 

However, it seems that this can only be judged one by one and cannot be performed in batches.

2.

SQL> select   nvl2(translate('333', '\1234567890 ', '\'), 'is   characters ', 'is   number ')   from   dual;NVL2(TRANSLATE('333','\1234567------------------------------is   numberSQL> select   nvl2(translate(333, '\1234567890 ', '\'), 'is   characters', 'is   number')   from   dual;NVL2(TRANSLATE(333,'\123456789------------------------------is   numberSQL> select   nvl2(translate('aaaaa', '\1234567890 ', '\'), 'is   characters', 'is   number')   from   dual;NVL2(TRANSLATE('AAAAA','\12345------------------------------is   charactersSQL> 

3. It is said that batch query is required. You can check it out first, but you have not tried it:

select   decode(nvl2(translate(column, '\1234567890 ', '\'), '0', '1') ,'1',column,'')  from   dual;

I wrote:

Update T_87_Mtbuf Set SpCode='' Where  nvl2(translate(SpCode, '\1234567890 ', '\'), 'is   characters', 'is   number ')='is   characters';

Add:

To_number (...) can only accept numbers such as 122 or numeric string '123456'. If 'aaa' is accepted, an error "invalid character" is returned ".