Systems Engineering and RDBMS

Isnumeric() function

Posted by decipherinfosys on April 1, 2007

While working on one of projects at the client sites (and this is a very common requirement), we again came across the scenario where a string column was being used to store data of any data-type : Number, date, alphanumeric etc..  And this client was using Oracle.  The requirement was to perform mathematical computations on those records that had a value that was numeric in this column.  Since Oracle does not have an isnumeric() function like SQL Server does, we wrote our own simple little helper function:

SQL> create or replace function isnumeric
( p_string in varchar2)
return boolean
l_number number;
l_number := p_string;
return TRUE;
when others then
return FALSE;

And after dealing with the immediate crisis, also advised the client that in order to correct this issue, the right solution is design the table correctly (since there were other de-normalization issues as well in this schema, the next version release was going to have some time devoted for normalization of the schema).   Numbers should be stored as numbers, dates should be stored as dates etc..  That way, there is no need for applying functions on columns, no funky sorting logic, no implicit conversions when doing the joins…

Sorry, the comment form is closed at this time.

%d bloggers like this: