字串轉換為數字時判斷非法字元並校正

--首先判斷哪些為不符合數字格式的字串
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/，如需轉載，請註明出處，否則將追究法律責任。