Re: Numeric string ??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Dec 1999 08:18:48 -0500
Message-ID: <pcms4sgg2452igqjtc07pvnlqed90n63h3@4ax.com>

A copy of this was sent to "Olivier BRUZEAUX" <obruzeau_at_telecom.capgemini.fr> (if that email address didn't require changing) On Wed, 8 Dec 1999 12:15:55 +0100, you wrote:

>
>
>I am looking for an sql command to test in my string is only numeric ??
>VARCHAR string [15]
>
>
>Olivier
>
>

An exception block would do this, something like:

tkyte_at_8i> variable string varchar2(25)
tkyte_at_8i> variable isAnumber number

  1 declare
  2 tmp number;
  3 begin

  4     tmp := to_number( :string );
  5     :isANumber := 1;
  6  exception
  7     when value_error then
  8        :isAnumber := 0;

  9* end;
tkyte_at_8i> /

PL/SQL procedure successfully completed.

tkyte_at_8i> print isAnumber

 ISANUMBER

---

         1

tkyte_at_8i> exec :string := 'not a number'

PL/SQL procedure successfully completed.

tkyte_at_8i> /

PL/SQL procedure successfully completed.

tkyte_at_8i> print isAnumber

 ISANUMBER

---

         0

tkyte_at_8i> exec :string := '1243'

PL/SQL procedure successfully completed.

tkyte_at_8i> /

PL/SQL procedure successfully completed.

tkyte_at_8i> print isAnumber

 ISANUMBER

---

         1

If I was in C however (looks like pro*c to me) i might use strtol or strtod (string to Long, string to Double) instead to avoid having to go over the net to verify a number.

--
See http://osi.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 Wed Dec 08 1999 - 07:18:48 CST