Hi Helmut!
-- here is a quick and dirty, but working example:
--snip
----------------------------------------------------------------------------
--------------------
create table t ( pk number(1) primary key, s varchar2(10), n number(10) );
insert into t (pk,s) values (1,'12345');
insert into t (pk,s) values (2,'text');
insert into t (pk,s) values (3,'123dfe');
insert into t (pk,s) values (4,'9876432');
commit;
-- conversion
BEGIN
FOR x IN ( select * from t) LOOP
BEGIN
-- try to convert string to number;
x.n := to_number(x.s);
UPDATE t set n=x.n where pk=x.pk;
EXCEPTION
WHEN value_error THEN
-- sorry, we couldn't convert, to_number gave us this error
NULL; -- dummy statement, just do nothing
END;
END LOOP;
COMMIT;
END;
/
select * from t;
PK S N
---------------- ---------- ----------------
1 12345 12345
2 text
3 123dfe
4 9876432 9876432
--snip
----------------------------------------------------------------------------
--------------------
q.e.d.
The idea is to catch the exception raised by to_number if it can't convert
the string.
Hope this helps,
Andreas
> ----------
> Von: Helmut Daiminger[SMTP:[EMAIL PROTECTED]]
> Gesendet: Donnerstag, 25. Januar 2001 01:40
> An: Multiple recipients of list ORACLE-L
> Betreff: Convert character string to number
>
> Hi!
>
> 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?
>
> This is 8.1.6 on Win2k.
>
> Thanks,
> Helmut
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Helmut Daiminger
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Haunschmidt Andreas VASL/FAS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
