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;
select isnumeric('10.1.1') from dual
select isnumeric('10.1') from dual
select isnumeric('-10.1') from dual
We hope this helps someone.