I offer in this Quseful (Quick and Useful) a package that you can use to determine if a string contains a valid integer, number, binary_float or binary_double (note: if you are not running Oracle 10g, you will need to comment out the binary_* versions in this package). It is based on code I wrote about back in 1997 (available here, along with the article I wrote about this topic, originally published...um....I am not sure where).
So you are now asking yourself: "Why the heck is Steven dredging up this dusty, old content?" The answer could be that Steven has run out of new things to write about, but that's not quite true. The real answer is that I visited everyone's favorite search engine the other day and searched for "PL/SQL Test". I found and followed a link to techonthenet.com, which offered a tip on how to test to see if a string was a valid number.
The online help topic is found here:
http://www.techonthenet.com/oracle/questions/isnumeric.php
and it suggests that, in brief, you use the TRANSLATE function to get the job done. That is exactly what I talked about not doing way back in 1997, so I thought I would offer this package to make sure anyone who needed it would have a good implementation.
Show me the code!
Download all the source code, plus the old article, and a Quest Code Tester test definition export file from this zip file.
Here is the basic idea behind the "is it a valid number?" algorithm: Why not let Oracle do the "heavy lifting"?
After all, it's not easy to determine if a string is a valid number. There are so many forms a number can take. And over time, Oracle could add support for other ways of specifying numbers. If I write an algorithm myself, I have to keep it up to date. Yuck.
So instead of doing that I will simply call Oracle's built-in TO_NUMBER function (or the appropriate variant for other datatypes); If that program doesn't raise an exception trying to convert the string to a number, well then, it must be a valid number!
Here's the code for one of the functions:
CREATE OR REPLACE PACKAGE BODY string_is
IS
FUNCTION valid_number (string_in IN VARCHAR2)
RETURN BOOLEAN
IS
l_dummy NUMBER;
l_is_number BOOLEAN DEFAULT FALSE;
BEGIN
IF string_in IS NOT NULL
THEN
l_dummy := TO_NUMBER (string_in);
l_is_number := TRUE;
END IF;
RETURN l_is_number;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END valid_number;
How do I use it?
It's pretty straightforward. Just pass any of the functions a string and it will return TRUE or FALSE, as in:
BEGIN
IF string_is.valid_integer (string_in)
THEN
... use it as an integer
ELSIF string_is.valid_number (string_in)
THEN
... use it as a number
And so on. Clearly, an integer will return TRUE for both "valid_integer" and "valid_number", so if you want to distinguish between the two, you will need to test for integer first.