Re: Checking for Numeric Values

Steven Moyano wrote:
>
> I am working with data that sometimes is entirely numeric, sometimes
> alphanumeric (300,350A,400F, for example).
>
> Is there a way to imitate the IsNumeric function in MS Basic, which returns a
> Yes/No depending on whether the value can be converted to a number?
>
> TO_NUMBER(350A) yields ORA-01722: invalid number, and the program stops.
>
> Thanks very much for your time and help.
>
> Steven Moyano
> Northwestern University, Evanston, IL. USA
> stevem_at_nwu.edu

The easiest way to achieve this is to write your own stored function in PL/SQL, using the TO_NUMBER built_in function as above, but with an exception routine which traps the above error and returns whatever you wish instead (?)
Don't forget to include the pragma <restrict_references> if you want to be able to use it SQL statements and if, as recommended, you make your function part of a package.

Hope this helps.

Chrysalis. Received on Thu Mar 20 1997 - 00:00:00 CST