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
  as
    l_ret number;
  begin
    -- Try to cast to a number
    begin
      l_ret := to_number(p_value);
      -- if the line above worked then just reset to 1
      l_ret := 1;
    exception
      when others then
        -- Something, anything, went wrong
        l_ret := 0;
    end;
    -- Return
    return l_ret;
  end isnumeric;

e.g

  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.