Here are three slightly different versions of what you might be looking for.
CREATE OR REPLACE FUNCTION isnum (thestring IN VARCHAR2)
RETURN BOOLEAN
IS
holder NUMBER;
BEGIN
holder := TO_NUMBER(thestring);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;
/
That is callable from PL/SQL, not SQL. For SQL use, you would return a number
or a string indicating whether it is successful.
CREATE OR REPLACE FUNCTION isnum (thestring IN VARCHAR2)
RETURN NUMBER
IS
holder NUMBER;
BEGIN
holder := TO_NUMBER(thestring);
RETURN 1; -- pseudo-true
EXCEPTION
WHEN OTHERS
THEN
RETURN 0; -- pseudo false
END;
/
However, the following might be slightly better, depending on the application.
CREATE OR REPLACE FUNCTION toNumIfPossible (thestring IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(thestring);
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/
Cheers
Bill
Woody Mckay wrote:
> Hello gurus,
>
> Could someone tell me the best way to check a string to ensure that it
> contains all numeric digits before converting it to a number with to_number?
> I don't see a function like ISNUM.
>
> TIA,
>
> Woody
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Woody Mckay
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
______________________________________________
http://www.datacraft.com/ http://plnet.org/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pribyl
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
