Re: RE: Convert character string to number

I think you have to crate a procedure with an IN and OUT parameter. In you IN parameter the row will serve as your input.

First get the length of the input and store as LENGTH_FIELD. Create a loop with a substr command

    substr(INPUT_VALUE,n,1) ==> area1 (put value in area1)     If area1 in (1,2,3,4,5,6,7,8,9,0)
    Then

          counter := counter + 1;
     If counter and LENGTH_FIELD are equal ...
          ...THEN your INPUT_VALUE may be converted to_number

ORACLE-L_at_fatcity.com wrote:
>
> Also the subtrng method rejects numbers written in scientific notation.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
>
> -----Original Message-----
> Sent: Thursday, January 25, 2001 8:02 AM
> To: Multiple recipients of list ORACLE-L
>
>
> On Wed, 24 Jan 2001, Helmut Daiminger wrote:
>
> > I want to read a column (varchar2) from a table and convert the contents
> > into numbers if the string consists of numbers only. If the string contains
> > characters, I don't want to convert it.
> >
> > Example for data in varchar2 colum:
> >
> > row 1:   12345
> > row 2:   text
> > row 3:   123dfe
> > row 4:   9876432
> >
> > I can easily conver row 1 and 4 using the to_number function, right? But how
> > can I tell Oracle to skip rows 2 and 3 since the character field also
> > contains characters not just numbers?
> >
> > Any idea?
>
> One of my favorite questions.  Why?
>
> Because there will inevitably be posts using substr() or something
> similar, and I get to shoot 'em down.  ;)
>
> Use of string functions will really slow down your code.  If you
> rely on internal oracle error trapping to do this, it will be
> pretty fast.
>
> Code below.
>
> Jared
>
> --------------------------------------------------------------------
>
>
> drop table num_test;
>
> create table num_test (
>     value varchar2(20) not null
> )
> /
>
> insert into num_test values('12345');
> insert into num_test values('text');
> insert into num_test values('123dfe');
> insert into num_test values('9876432');
>
>
> create or replace function is_number( chk_data_in varchar2 )
> return integer
> is
>     dummy number(38,4);
> begin
>     dummy := to_number(chk_data_in);
>     return 1;
> exception
> when value_error then
>     return 0;
> when others then
>     raise;
> end;
> /
>
> show errors function is_number
>
>
> -- the +10 in the second column serves to prove that
> -- conversion is only taking place on numeric values
> select
>     decode(is_number(value), 0, value, 1, to_number(value)),
>     decode(is_number(value), 0, value, 1, to_number(value) + 10)
> from num_test
> /
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: jkstill_at_cybcon.com
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
>   INET: ian_at_SLAC.Stanford.EDU
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>