Warning this is going to take long.....not advisable for large data
ORACLE-L_at_fatcity.com wrote:
>
> 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).
> >
> __________________________________________________________________
> Get your own FREE, personal Netscape Webmail account today at
> http://webmail.netscape.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: bmarialuz_at_netscape.net
>
> 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).
Received on Thu Jan 25 2001 - 12:24:32 CST
