OraFAQ Forum: Server Utilities » To

Home » RDBMS Server » Server Utilities » To_number conversion (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To_number conversion [message #479273]

Fri, 15 October 2010 14:30

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.

Report message to a moderator

Re: To_number conversion [message #479279 is a reply to message #479273]

Fri, 15 October 2010 14:45

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')"

Report message to a moderator

Re: To_number conversion [message #479283 is a reply to message #479279]

Fri, 15 October 2010 15:08

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????

Report message to a moderator

Re: To_number conversion [message #479284 is a reply to message #479283]

Fri, 15 October 2010 15:10

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.

Report message to a moderator

Re: To_number conversion [message #479289 is a reply to message #479273]

Fri, 15 October 2010 15:19

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select to_number('$350,000','$999,999') val from dual;
       VAL
----------
    350000

Regards
Michel

Report message to a moderator

Re: To_number conversion [message #479290 is a reply to message #479273]

Fri, 15 October 2010 15:20

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: To_number conversion [message #479294 is a reply to message #479290]

Fri, 15 October 2010 15:42

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: To_number conversion [message #479590 is a reply to message #479294]

Mon, 18 October 2010 08:11

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.

Report message to a moderator

Re: To_number conversion [message #479610 is a reply to message #479590]

Mon, 18 October 2010 09:22

Barbara Boehmer
Messages: 9101
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 10:20:59 CST 2024