CODEWITHSUNDEEP
bashshelloracle11gsql-loader
kattashri
kattashri

Reputation: 267

concatenate multiple fields in sqlldr

I am working on sqlldr(sql loader) in oracle 11g. I am trying to concatenate 3 fields into a single field. Has anyone done this?

ex: TABLE - "CELLINFO" where the fields are (mobile_no,service,longitude).

The data given is (+9198449844,idea,110,25,50) i.e. (mobile_no,service,grad,min,sec).

But while loading data into the table i need to concatenate the last 3 fields (grad,min,sec) into the longitude field of the table.

Here i cant edit manually because i have 1000's of data to be loaded.

I also tried using ||,+ and concat().... but I am not able to.

Upvotes: 2

Views: 10443

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

ctl may be:

load data
append
into table      cellinfo
fields terminated by ","
(
mobile_no,
service,
grad BOUNDFILLER,
min BOUNDFILLER,
sec BOUNDFILLER,
latitude ":grad || :min|| :sec"
)

suposing cellinfo(mobile_no, service, latitude).

Some nice info here on orafaq

Alternatively, you can modify your input:

awk -F"," '{print $1","$2","$3":"$4":"$5}' inputfile > outputfile

Upvotes: 6

Related Questions