| sql loader, % conversion and $ removal [message #477579] |
Fri, 01 October 2010 17:02  |
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 #477581 is a reply to message #477580] |
Fri, 01 October 2010 17:41  |
 |
Barbara Boehmer
Messages: 9101
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
