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.
create or replace
function isnumeric
( inputstring in varchar2
) return varchar2 as
v_length int;
begin
v_length := LENGTH(TRIM(TRANSLATE(inputstring, ' +-.0123456789', ' ')));
if v_length = 0 OR v_length = '' OR v_length IS NULL then
return 'true';
else
dbms_output.put(v_length);
return 'false';
end if;
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.
I will need to look into this more, using Regular Expressions and see if I can come up with something better.
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;
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;