Home » RDBMS Server » Server Utilities » To_number conversion (Oracle 10g)
To_number conversion [message #479273] Fri, 15 October 2010 14:30 Go to next message
simplesql
Messages: 20
Registered: October 2010
Junior Member
Hi,
I am sql loading a csv file.
My data has a salary column which comes with dollar symbol
Sal
$5,000
$10,000
$350,000
i want to eliminate $ symbol and load the amt as number in teh database table. I used the following command and getting an error:

Load data.....
fields terminated by ','.....
(Name
Salary "To_number(trim('$' FROM :Salary), '999,999.99')"
)

Can someone say what is wrong here or how to do it?

thanks in advance.
Re: To_number conversion [message #479279 is a reply to message #479273] Fri, 15 October 2010 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is possible to use the TO_CHAR operator to store formatted dates and numbers. For example:

field1 ... "TO_CHAR(:field1, '$09999.99')"
Re: To_number conversion [message #479283 is a reply to message #479279] Fri, 15 October 2010 15:08 Go to previous messageGo to next message
simplesql
Messages: 20
Registered: October 2010
Junior Member
I think u dint get it completely, I need to trim the $ symbol and then display it as a number.
However, I tried using To_char conversion as u suggested dint wrk. ... any other leads????
Re: To_number conversion [message #479284 is a reply to message #479283] Fri, 15 October 2010 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

my car doesn't work
tell me how to make my car go.

By the way "u" is not a member of this forum.
Re: To_number conversion [message #479289 is a reply to message #479273] Fri, 15 October 2010 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_number('$350,000','$999,999') val from dual;
       VAL
----------
    350000

Regards
Michel
Re: To_number conversion [message #479290 is a reply to message #479273] Fri, 15 October 2010 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
fields terminated by ','.....
(Name
Salary "To_number(trim('$' FROM :Salary), '999,999.99')"


If a field terminates by a comma then by definition it cannot contains a comma.

Regards
Michel

Re: To_number conversion [message #479294 is a reply to message #479290] Fri, 15 October 2010 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> desc test
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 SAL                                    NUMBER

SQL>

Control file:
load data
infile *
replace
into table test
( 
  sal char terminated by whitespace "to_number(:sal, '$999,999')" 
)

begindata
$5,000
$10,000
$350,000

Loading & the result:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pet Lis 15 22:42:15 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from test;

       SAL
----------
      5000
     10000
    350000

SQL>
Re: To_number conversion [message #479590 is a reply to message #479294] Mon, 18 October 2010 08:11 Go to previous messageGo to next message
simplesql
Messages: 20
Registered: October 2010
Junior Member
Thanks Little foot. Your 'Char' description for the column in ctl file worked the magic...

sal char terminated by whitespace "to_number(:sal, '$999,999')"

I have a small problem with incoming file. The incoming file
has a zero vaule for the salary column but the zero is not enclosed in quotes.
1. Does this mean the salary is not a character and is a number here?
2. If so how should I modify my to_number statment???

NoTE:
sample data file ;
Sal
"$5,000 "
$0
"$10,000 "
'$350,000 "
I did a trim to remove the blank space after the sal amt.
Re: To_number conversion [message #479610 is a reply to message #479590] Mon, 18 October 2010 09:22 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl:
load data
infile *
replace
into table test
fields terminated by whitespace
optionally enclosed by '"'
trailing nullcols
(sal char "to_number (replace (translate (:sal, '$,', ' '), ' '))")
begindata:
"$5,000 "
$0
"$10,000 "
"$350,000 "


SCOTT@orcl_11gR2> create table test
  2    (sal  number)
  3  /

Table created.

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from test
  2  /

       SAL
----------
      5000
         0
     10000
    350000

4 rows selected.
SCOTT@orcl_11gR2>

Previous Topic: CLOB inserted as varchar2
Next Topic: Where clause in control file
Goto Forum:
  


Current Time: Fri Sep 17 08:25:36 CDT 2021