Re: does string contain a valid number

From: Adric Norris <spikey.mcmarbles_at_gmail.com>
Date: Tue, 18 Aug 2009 15:16:50 -0500
Message-ID: <d4beff360908181316t4c490a00qf6007309c1cb3bc8_at_mail.gmail.com>

On Tue, Aug 18, 2009 at 13:19, Michael Moore <michaeljmoore_at_gmail.com>wrote:

> What is the best way to see if a string contains a valid number? 10gR2
>
> thanks,
> Mike
>

The best solution is, of course, to simply use the correct datatype in the first place. When it's too late for the up-front approach, I've occasionally seen a function similar to the one below used as a workaround.

create or replace function is_number(p_number IN varchar2, p_format IN varchar2 default NULL)

   return number
IS

   v_number number;
BEGIN
   BEGIN

      if p_format is NULL then
         v_number := to_number(p_number);
      else
         v_number := to_number(p_number, p_format);
      end if;
   EXCEPTION
      when VALUE_ERROR then
         v_number := NULL;

   END;
   return v_number;
END;
/

An alternative is to validate the string using a regular expression, but this can get rather awkward if the numeric representations aren't fairly uniform and straightforward.

Regardless of the method chosen, you'll pay a steep performance penalty if running against a large volume of data.

-- 
"I'm too sexy for my code." - Awk Sed Fred.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 18 2009 - 15:16:50 CDT