Home » RDBMS Server » Server Utilities » sql loader, % conversion and $ removal
sql loader, % conversion and $ removal [message #477579] Fri, 01 October 2010 17:02 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
I have my control file like this

load data
into table mytable
replace
---
---
col1,
col2..

Now if the data is something like this:

"$23,555", 5.44%

How to remove $ and convert the % and show (0.0544) ?

how to modify the ctl file accordingly for col1, and col2 assuming col1 needs to store the 23555 and col2 needs to store 0.0544
Re: sql loader, % conversion and $ removal [message #477580 is a reply to message #477579] Fri, 01 October 2010 17:13 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/
>How to remove $ and convert the % and show (0.0544) ?
Any text editor will suffice or better yet have the file created so no modification is required.
>how to modify the ctl file accordingly for col1, and col2 assuming col1 needs to store the 23555 and col2 needs to store 0.0544
Ignore the extraneous characters & divide col2 by 100.
Re: sql loader, % conversion and $ removal [message #477581 is a reply to message #477580] Fri, 01 October 2010 17:41 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl:
load data
infile *
into table mytable
replace
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(col1 "to_number (replace (ltrim (:col1, '$'), ',', ''))"
,col2 "to_number (rtrim (:col2, '%')) * .01"
)
begindata:
"$23,555", 5.44%


SCOTT@orcl_11gR2> create table mytable
  2    (col1  number,
  3  	col2  varchar2 (10))
  4  /

Table created.

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

SCOTT@orcl_11gR2> select to_char (col1, '99,999') col1,
  2  	    to_char (col2, '0.9999')  col2
  3  from   mytable
  4  /

COL1    COL2
------- -------
 23,555  0.0544

1 row selected.

SCOTT@orcl_11gR2>

Previous Topic: export
Next Topic: Why my $ORACLE_HOME/rdbms/demo directory has no ulcase* files ? Please help me !
Goto Forum:
  


Current Time: Thu Mar 28 16:09:37 CDT 2024