Oracle-L: RE: Datatype conversions

From: Rajesh Dayal <rajesh_at_iitcoman.com>
Date: Mon, 03 Nov 2003 05:14:25 -0800
Message-ID: <F001.005D5735.20031103051425@fatcity.com>

This should be helpful,

SQL> create table tt1 (c1 varchar2 (30));

Table created.
SQL> insert into tt1 values ('123,50');

1 row created.

SQL> insert into tt1 values ('44567,9');

1 row created.

SQL> insert into tt1 values ('1,23456');

1 row created.

SQL> commit ;

SQL> select * from tt1 ;

123,50
44567,9
1,23456

SQL> l
1 select to_number ( substr(c1,1,(instr(c1,',')-1))||'.'|| 2 substr(c1,(instr(c1,',')+1),length(c1)) 3 ) converted_number
4* from tt1
SQL> / CONVERTED_NUMBER

           123.5
         44567.9
         1.23456

SQL> create table tt2 (c1 number (15,5));

Table created.

SQL> insert into tt2 value (select to_number ( substr(c1,1,(instr(c1,',')-1))||'.'||   2 substr(c1,(instr(c1,',')+1),length(c1))   3 ) converted_number
  4 from tt1)
  5 /

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from tt2 ;

     123.5
   44567.9
   1.23456

SQL> HTH,
Rajesh

Rajesh Dayal
Senior Oracle DBA (OCP 8,8i,9i)
International Information Technology Company LLC

-----Original Message-----

Sent:	Monday, November 03, 2003 4:30 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Datatype conversions

Hallo,

Maybe this sounds simple for all of you, but I have a table with the field PRICE and that is with datatype varchar2 I want the data in that field to be inserted in another table with field PRICE, but that field has the datatype NUMBER. How can I
easiest do this sql statement?

In the varchar2(30) field PRICE , table1 it looks like this

PRICE 12,50
37,50

The datype in table 2 is for that field NUMBER(7,2)

Roland

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: roland.skoldblom_at_ica.se

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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.net
--

Author: Rajesh Dayal
INET: rajesh_at_iitcoman.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------