需求:
如果字段值不是数字或者数字型字符串,把该字段值update成''
1.函数法(不能批量,舍弃):
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 ;
但是这个好像只能一个个判断,不能批量。
2.
SQL> select nvl2(translate('333', '\1234567890 ', '\'), 'is characters ', 'is number ') from dual;
NVL2(TRANSLATE('333','\1234567
------------------------------
is number
SQL> select nvl2(translate(333, '\1234567890 ', '\'), 'is characters', 'is number') from dual;
NVL2(TRANSLATE(333,'\123456789
------------------------------
is number
SQL> select nvl2(translate('aaaaa', '\1234567890 ', '\'), 'is characters', 'is number') from dual;
NVL2(TRANSLATE('AAAAA','\12345
------------------------------
is characters
SQL>
3.据说要批量,可以这样先查出来,没有试过:
select decode(nvl2(translate(column, '\1234567890 ', '\'), '0', '1') ,'1',column,'') from dual;
我写的:
Update T_87_Mtbuf Set SpCode='' Where nvl2(translate(SpCode, '\1234567890 ', '\'), 'is characters', 'is number ')='is characters';
补充一点:
to_number(...)中只能接受数字如122或数字型字符串'122',接受'aaa'会报错“无效字符”。