字符串转换为数字时判断非法字符并校正

--首先判断哪些为不符合数字格式的字符串
CREATE OR REPLACE FUNCTION "HLJHZ"."FUN_NOTVALID" (MYARG IN VARCHAR2)
RETURN VARCHAR2
AS
  v_count number(10);
  v_number varchar2(30);
  v_char number;
  C_NOTVALID varchar2(10) :='非法';
  not_valid exception;
  pragma exception_init(not_valid,-01722);
BEGIN
   select to_number(MYARG) into v_char from dual;
   return ('合法');
   exception
      when not_valid then
      RETURN(C_NOTVALID);
END;
/

--将不符合数字格式的字符串转换为符合数字格式的字符串，然后再使用to_number函数转换即可
CREATE OR REPLACE FUNCTION "HLJHZ"."FUN_CHAR_NUMBER" (MYARG IN VARCHAR2)
RETURN VARCHAR2
AS
  v_count number(10);
  v_number varchar2(30);
  v_valid number;
  v_char varchar2(30);
  not_valid exception;
  pragma exception_init(not_valid,-01722);
BEGIN
   if MYARG='-' then
   select '0' into v_char from dual;
   else select MYARG into v_char from dual;
   end if;
   select to_number(v_char) into v_valid from dual;
   return (v_char);
   exception
      when not_valid then
      --对包含不符合数字格式的字符串进行逐一处理，如果遇到新的不合法的情况需要补充

      select trim(v_char) into v_char from dual;
      select TRANSLATE(v_char,',。、万元','.') into v_char from dual;
      select substr(v_char,1,instr(v_char,'.',1,1))||
replace(substr(v_char,instr(v_char,'.',1,1)+1),'.','') into v_char from dual;
      select replace(v_char,'　','') into v_char from dual;
      select replace(v_char,'－','-') into v_char from dual;
      select replace(v_char,'—','-') into v_char from dual;
      select substr(v_char,1,instr(v_char,'-',1,1))||
replace(substr(v_char,instr(v_char,'-',1,1)+1),'-','') into v_char from dual;
      select replace(v_char,'0-','-') into v_char from dual;
      RETURN(v_char);
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28803801/viewspace-768148/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28803801/viewspace-768148/