Home » RDBMS Server » Server Utilities » Concatenating dashes in a column using sqlldr ( 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production)
Concatenating dashes in a column using sqlldr [message #508379] Fri, 20 May 2011 13:17 Go to next message
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 #508380 is a reply to message #508379] Fri, 20 May 2011 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What datatype is SSN?
Re: Concatenating dashes in a column using sqlldr [message #508381 is a reply to message #508380] Fri, 20 May 2011 13:21 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
It's varchar.

Thanks
Re: Concatenating dashes in a column using sqlldr [message #508401 is a reply to message #508381] Fri, 20 May 2011 17:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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>

Re: Concatenating dashes in a column using sqlldr [message #509267 is a reply to message #508401] Thu, 26 May 2011 14:42 Go to previous message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hello Barbara,

Sorry for the late reply back. I didn't know that it would work there as well. I see that it does. Embarassed Thanks again !

Joe
Previous Topic: SQL Loader's SQL string issue
Next Topic: Export/Import
Goto Forum:
  


Current Time: Thu Mar 28 12:36:22 CDT 2024