Re: does string contain a valid number
Date: Tue, 18 Aug 2009 14:15:34 -0700
I agree, best solution would have been to go with the proper datatype in the first place. Unfortunately, it's not really an option in my case. thanks for the input,
On Tue, Aug 18, 2009 at 1:16 PM, Adric Norris <spikey.mcmarbles_at_gmail.com>wrote:
> 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
> 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.