Re: Numeric string ??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Dec 1999 07:46:31 -0500
Message-ID: <809v4so52asl6c4lodv32bks7tor0seh2b@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 Thu, 9 Dec 1999 11:19:38 +0100, you wrote:

>I want to do that in a loop, I tried this but without success:
>
>BEGIN
>
>DELETE FROM OBR ;
>DECLARE
>
>my_abonum filesms.abonum%TYPE;
>tmp number;
>num number;
>CURSOR c1 IS select abonum from filesms where fildatenit = 2147483647 and
>abonu
>m like '3360000%' ;
>
>
>BEGIN
>
>DELETE FROM OBR ;
>
>OPEN c1;
>LOOP
> BEGIN
> FETCH c1 INTO my_abonum ;
>
> SELECT to_number (abonum)
> INTO tmp
> FROM FILESMS
> WHERE ABONUM = my_abonum ;
>
>
> EXCEPTION
> WHEN INVALID_NUMBER THEN
> INSERT INTO obr VALUES (my_abonum) ;
> WHEN NO_DATA_FOUND THEN
> EXIT ;
> END ;
>END LOOP ;
>CLOSE C1;
You are missing another END right here.

>END;
>/
>

I would suggest that the following might be easier:

tkyte_at_8.0> create table filesms ( abonum varchar2(25), fildatenit number ); Table created.

tkyte_at_8.0> create table obr ( abonum varchar2(25) ); Table created.

tkyte_at_8.0> create or replace function is_number( str in varchar2 ) return number   2 is
  3 x number;
  4 begin

  5          x := to_number(str);
  6          return 1;
  7  exception
  8          when value_error then
  9                  return 0;

 10 end;
 11 /
Function created.

tkyte_at_8.0> insert into filesms values ( '33600000', 2147483647 ); 1 row created.

tkyte_at_8.0> insert into filesms values ( '3360000a', 2147483647 ); 1 row created.

tkyte_at_8.0> delete from obr;
0 rows deleted.

tkyte_at_8.0> insert into obr
  2 select abonum
  3 from filesms
  4 where is_number( abonum ) = 0

  5     and fildatenit = 2147483647
  6     and abonum like '3360000%'

  7 /

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select * from obr;

ABONUM

---

3360000a

>
>Kenneth C Stahl <BlueSax_at_Unforgettable.com> a écrit dans le message :
>384E5E95.79FB2E6C_at_Unforgettable.com...
>> Olivier BRUZEAUX wrote:
>> >
>> > I am looking for an sql command to test in my string is only numeric ??
>> > VARCHAR string [15]
>> >
>> > Olivier
>>
>> If you are talking about plsql, then do it like this:
>>
>> Function Is_Number(Buff varchar2) return integer is
>> Dummy Number := 0;
>> begin
>> Dummy := Buff;
>> return(0);
>> exception
>> when invalid_number then
>> return(1);
>> when others then
>> return(2)
>> end;
>>
>> If you pass a numeric string to the function it will return 0 if the value
>> was truely numeric, if it is an invalid number it will return 1. If there
>> was some other error it will return 2.
>

--
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 Thu Dec 09 1999 - 06:46:31 CST