Oracle ‘isnumeric’ function

Oracle ‘isnumeric’ function

A possible implementation is to simply try to assign the string to a number and catch any exceptions. If something goes wrong then it's not a number!


  create or replace function isnumeric(p_value in varchar2) return number
    l_ret number;
    -- Try to cast to a number
      l_ret := to_number(p_value);
      -- if the line above worked then just reset to 1
      l_ret := 1;
      when others then
        -- Something, anything, went wrong
        l_ret := 0;
    -- Return
    return l_ret;
  end isnumeric;


  select isnumeric('10.1.1') from dual
  select isnumeric('10.1') from dual
  select isnumeric('-10.1') from dual

We hope this helps someone.

Comments (1)

  1. Paul Ayling:
    Sep 17, 2013 at 04:06 PM

    test did you get this Steve?

Add a Comment

Please login to comment.