Re: does string contain a valid number

From: Adric Norris <>
Date: Tue, 18 Aug 2009 15:16:50 -0500
Message-ID: <>

On Tue, Aug 18, 2009 at 13:19, Michael Moore <>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

   v_number number;

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

   return v_number;

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.

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

Original text of this message