Oracle IsNumeric Function

So, most people that use Oracle and other RDBMS know that they severly lack some very basic functions that exist in the middle-tier langauges we usually use. But sometimes we can;t use our middle-tier languages.  Right now I am writing a Stored Procedure in Oracle that will import, validate and normalize 1,000,000 records from raw text files.  This is WAY TOO big a job for ColdFusion, so I am using Oracle Stored Procudres and functions.

During my error checking I realized I needed some extra functionality. I needed IsDate() and IsNumeric(). With some Googling and simple Oracle functions, I was able to create both. today I will demostrate IsNumeric(). I am still working on adding more functionality to IsDate() and I will demo that one another day.


UPDATE: I have updated this post to reflect a better way of handling an IsNumeric() function. This is from Alexandre Paiva's comment, thank you Alexandre!

    CREATE OR REPLACE FUNCTION isnumeric (p_string_value IN VARCHAR2)RETURN BOOLEAN
    AS

    test_value NUMERIC;
    BEGIN
        BEGIN
            test_value := TO_NUMBER (p_string_value);
            RETURN (true);

            EXCEPTION
            WHEN OTHERS THEN
            RETURN (false);
        END;
    END isnumeric;

This very simple function takes in a string of characters and passes it in. Then using the Oracle TRANSLATE function, it replaces any characters that are 1-9 or a decimal, plus sign or minus sign with a space (" '). Then it does a trim on the remaining string, if the value is all spaces, the Length() function that wraps the others will return 0 and we know that the string that was passed in is numeric. If it returns 1 or more then we know there were non-numerics and we can return false.

Comments
vojta's Gravatar Hi, this will accept date values stored as strings. For example 10.20.2008 is recognized as numeric value too.
# Posted By vojta | 6/12/08 10:33 AM
Jason Dean's Gravatar @vojta - You're right. And actually, it would problem recognize 4+ 555-2.5..88877--+ as a number too.

I will need to look into this more, using Regular Expressions and see if I can come up with something better.
# Posted By Jason Dean | 6/12/08 11:09 AM
Noname's Gravatar Consider:

create or replace function isnumeric( p_string_value in varchar2 )
return varchar2 as
test_value numeric;
begin
begin
test_value := to_number( p_string_value );
exception
when others then
return( 'false' );
end;
return( 'true' );
end isnumeric;
# Posted By Noname | 8/11/08 11:17 AM
Alexandre Paiva's Gravatar I made some changes to use a boolean version. Thanks...
CREATE OR REPLACE FUNCTION isnumeric (p_string_value IN VARCHAR2)
RETURN BOOLEAN
AS
test_value NUMERIC;
BEGIN
BEGIN
test_value := TO_NUMBER (p_string_value);
RETURN (true);
EXCEPTION
WHEN OTHERS THEN
RETURN (false);
END;
END isnumeric;

grant all on isnumeric to public ;
set serveroutput on;

begin
if isnumeric('12233,33') then --brazilian number system: decimal point is comma.
dbms_output.put_line(' Is a number ');
else
dbms_output.put_line(' Is not a number ');
end if;
end;
# Posted By Alexandre Paiva | 9/5/08 12:11 PM
Jason Dean's Gravatar @Alexandre - Very nice. Thank you. I did not realize you could use Boolean values in Oracle. Every time I have tried it, I have failed at getting it to work. This is great and will cause me to have slightly less disdain for working in Oracle. Not to go back and refactor my existing procedures to use this version.
# Posted By Jason Dean | 9/9/08 8:54 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner