Re: How to test for all digits in string

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: wmckay_at_hydrogenmedia.com
>
> 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: ListGuru_at_fatcity.com (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: bill_at_datacraft.com

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: ListGuru_at_fatcity.com (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).