Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Please Help: Check to see if a string is a valid number in proced ure/function.
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;
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