AW: Convert character string to number

From: Haunschmidt Andreas VASL/FAS <Andreas.Haunschmidt_at_voest.co.at>
Date: Wed, 31 Jan 2001 23:19:27 -0800
Message-ID: <F001.002A6CDA.20010131205105@fatcity.com>

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:hdaiminger_at_vivonet.com]
> 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: hdaiminger_at_vivonet.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: Haunschmidt Andreas VASL/FAS
  INET: Andreas.Haunschmidt_at_voest.co.at

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 Feb 01 2001 - 01:19:27 CST