| Concatenating dashes in a column using sqlldr [message #508379] |
Fri, 20 May 2011 13:17  |
rappaj
Messages: 147
Registered: June 2010
Location: NYC
|
Senior Member |
|
|
Hi,
I'd like to load ss_numbers, but concatenate dashes in between.
The ssn currently looks like this in the file: 123456789
I'd like to put dashes in there to make it load like this.
123-45-6789
The data is positional, so I have the column in the ctl file defined as:
ssn position(1:9) char nullif ssn=blanks ,
I know how to concat using the other method of loading this way, but this is not how the data file looks:
"substr(lpad(:ssn,9,'0'),1,3)||'-'||substr(lpad(:ssn,9,'0'),4,2) ||'-'||substr(lpad(:ssn,9,'0'),6,4)",
Can someone please show me how to get the dashes in there while loading with the positional method?
Thanks
|
|
|
|
|
|
|
|
| Re: Concatenating dashes in a column using sqlldr [message #508401 is a reply to message #508381] |
Fri, 20 May 2011 17:09  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It should not make any difference. You should be able to treat positional data, as shown below, the same as delimited data.
-- test.dat:
123456789 aa
987654321 cc
-- test.ctl:
load data
infile test.dat
into table test_tab
fields
(ssn position(1:9) char nullif ssn=blanks "substr(lpad(:ssn,9,'0'),1,3)||'-'||substr(lpad(:ssn,9,'0'),4,2) ||'-'||substr(lpad(:ssn,9,'0'),6,4)",
other position(11:12) char)
-- table, load, and results:
SCOTT@orcl_11gR2> create table test_tab
2 (ssn varchar2 (11),
3 other varchar2 (2))
4 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from test_tab
2 /
SSN OT
----------- --
123-45-6789 aa
987-65-4321 cc
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Thanks again !