Oracle: Function ISNUMERIC Check Value is Number or Not

CREATE OR REPLACE FUNCTION ISNUMERIC (PARAM IN CHAR) RETURN NUMBER AS
    DUMMY VARCHAR2(100);
BEGIN
    DUMMY:=TO_CHAR(TO_NUMBER(PARAM));
    RETURN (1);
EXCEPTION
    WHEN OTHERS THEN
        RETURN (0);
END;

EXAMPLE:
SELECT * FROM EMP WHERE ISNUMERIC(PHONE) = 1;



Related Post:
- ORA-06502: PL/SQL: numeric or value errorstring
- Oracle: Extract Numbers from String

15 comments:

  1. thats pretty bad practice to use exception handling to determine something that can be figured out by calculation. Mainly becuase exception handling invokes an extra performance overhead. This function below works about twice as fast.

    LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null

    ReplyDelete
    Replies
    1. Please check your code is not working, it is saying that 'A1B23C456' is numeric

      with t as
      (
      select 'A1B23C456' myfield from dual
      )
      select myfield from t
      where LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null

      Please note: here we are checking that is the field has the value which which can be converted to number.

      Delete
    2. Replace the last parameter with 13 (0 through 9, + - and period) blanks within quotes

      Delete
  2. Nimish Garg, you have misinterpreted this very simple code fragment. The code you have is selecting rows that are NOT numeric - you add a space for every field that is not numeric. So in your example you are looking for values that are Not numeric. If you want values that ARE numeric, simply change your 'Is Not Null' to 'Is Null'

    ReplyDelete
  3. Clearly this is exactly what I call a terrific article! Do you use this domain for your private joy exclusively or you actually have it to get profit with its help?

    ReplyDelete
  4. with t as
    (
    select 'A1B23C456' myfield from dual
    )
    select myfield from t
    where LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null actually this is working. But to find numeric values you should check is null.

    ReplyDelete
  5. The bad thing with this type of "parsing" data to be numeric is that the following will also result in "null" which means "numeric":
    select LENGTH(TRIM(TRANSLATE ('-.+56', ' +-.0123456789',' '))) from dual;

    ReplyDelete
  6. And spaces are also considered numerical....

    ReplyDelete
  7. For more thorough checking (i.e. '+123.4' is numeric, '+123..4' is non numeric):

    SELECT DECODE(TRANSLATE(DECODE(INSTR(:VAL,'.',1,2),0,
    DECODE(INSTR(:VAL,'+',1,2),0,
    DECODE(INSTR(:VAL,'-',1,2),0,:VAL
    ,'X')
    ,'X')
    ,'X'),'X+-0123456789.','X'),NULL
    ,DECODE(LENGTH(TRANSLATE(:VAL,'+-0123456789.','+-')),2,'NON_NUMERIC','NUMERIC'),'NON_NUMERIC') NUMERIC_VALUE
    FROM DUAL;

    ReplyDelete
    Replies
    1. it say that 1+ and + and - is numeric. I assume correct is:

      SELECT
      case
      when instr(trim(:VAL), '+') > 1 then 'NON_NUMERIC'
      when instr(trim(:VAL), '-') > 1 then 'NON_NUMERIC'
      else DECODE(
      TRANSLATE(DECODE(INSTR(:VAL,'.',1,2),0,
      DECODE(INSTR(:VAL,'+',1,2),0,
      DECODE(INSTR(:VAL,'-',1,2),0, :VAL, 'X'), 'X'),'X'),
      'X+-0123456789.','X'),NULL,
      DECODE(LENGTH(TRANSLATE(:VAL,'+-0123456789.','+-')),2,'NON_NUMERIC','NUMERIC'),'NON_NUMERIC')
      end NUMERIC_VALUE
      FROM DUAL;

      Delete
  8. create function isnumeric (testStr in varchar2) return boolean
    is
    begin
    return testStr is not null and regexp_like(testStr,'^-?[0-9]*\.?[0-9]*$');
    end;

    ReplyDelete
  9. It is a bad idea that using "LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null" to judge a string is a number or not.
    May I ask what will happen when myfield is some strings like '123-456' or '99.99.99'?

    ReplyDelete
  10. โปรโมชั่น pg slot มากมาย เล่นง่ายจ่ายจริง แตกจริง ต้อง pg slot เท่านั้น! เล่นสล็อต พีจีสล็อต เว็บไซต์ตรงผู้ให้บริการเกมสล็อตออนไลน์ชั้นหนึ่ง ทกลอง เล่น ฟรี พร้อมโบนัส

    ReplyDelete