Hello,
To me a simple, yet glaring, omission from the Oracle PL/SQL language, which is in many ways excellent, is a function to test to see if a string represents numeric data as can be found in many other languages (e.g is_numeric in PHP, isNumeric in Java and .Net).
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.
