CODEWITHSUNDEEP
sqloracle-databasesql-server-2008oracle11goracle-sqldeveloper
Bala S
Bala S

Reputation: 523

How to concatenate two field in single column in oracle SQLLDR control file?

My raw text file like below (having two fields (ACC and INT))

ACC INT

1160 1291

1160 1291

1160 1291

1160 1291

My table -> create table product(product_CODE number);

Control file -> load data

infile 'E:\SQLLDR\product.txt' "str '\r\n'"

append into table product

fields

trailing nullcols (

product_code position (1:9)

)

Requirement : Need product code without space like below

select * from product;

PRODUCT_CODE

11601291

11601291

11601291

11601291

how to achieve this?

Upvotes: 1

Views: 768

Answers (2)

PandaCheLion
PandaCheLion

Reputation: 476

What about something like this?

LOAD DATA 
INFILE * 
append into table product
( ACC FILLER 
  INT FILLER 
  PRODUCT_CODE  VARCHAR "UPPER(:ACC || :INT)" 
)

Upvotes: 1

Popeye
Popeye

Reputation: 35920

I think you need to use the REPLACE function here considering it as a string as follows:

product_code position (1:9) "REPLACE(:PRODUCT_CODE,' ','')"

I have not tested it but you can leave a comment if you find any issue with this solution

Upvotes: 1

Related Questions