Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please Help: Check to see if a string is a valid number in proced ure/function.

Re: Please Help: Check to see if a string is a valid number in proced ure/function.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 16 Aug 1999 20:33:48 GMT
Message-ID: <37c27598.34579402@newshost.us.oracle.com>


A copy of this was sent to "Justin J. Risser" <JJRisser_at_lancasterlabs.com> (if that email address didn't require changing) On Mon, 16 Aug 1999 15:40:24 -0500, you wrote:

>Is there a way to gracefully check to see if a string can be converted
>to a number in a procedure or function? I have tried using the
>TO_NUMBER(), but that just dies when I send it a string with letters in
>it. Is there a way to trap that error, or another way to check to see
>if a string is a valid number? Thank you.
>
>Justin

just try to convert it and if it works -- its a number, if not it is not a number. for example:

SQL> set serveroutput on
SQL> set echo on
SQL> @test
SQL> declare
  2          string  varchar2(25);
  3  begin
  4          string := 'abc';
  5  
  5          declare
  6                  x       number;
  7          begin
  8                  x := to_number(string);
  9                  dbms_output.put_line( 'String is a number' );
 10          exception
 11                  when value_error then
 12                          dbms_output.put_line( 'String is not a number' );
 13          end;

 14 end;
 15 /
String is not a number

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 16 1999 - 15:33:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US