Re: Question? Numeric Functions

In article <3ADD028B.2B51A249_at_pacbell.net>, Ray Vonhollen <RVONHOLLEN_at_pacbell.net> writes:
>Is there any good numeric function available that can check to see if a
>varchar2 or char column contains a pure numeric number? Preferably, a
>function that can be checked by an If Statement without generating a
>PLS-00382 error. It would only execute the preceding Block if statements
>and no execute upon failure.
>
>As an example a column containing the values '102-1' would generate an
>error in using the TO_NUMBER() function, but a column containing the
>values '102.1' would not.
>
>Any help would be greatly appreciated.
>
>Ray-

Ray, use exception logic around the to_number after using rtrim and ltrim to remove any excess characters from the column. You can use instr and substr to remove $ and commas etc...

begin
 v_fld := to_number(variable);
exception
 when others then non-numeric-logc
end;

You will probably want to make the edit routine a procedure within your code so that you can call it from right before you need to use it and can include any trimming, substringing etc... that you need to perform. Your procedure would probably look something like
 (v_success_indicator, v_char_value, v_number)

I hope this helps.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net