Home » RDBMS Server » Server Utilities » How to trim newlineChar,blanks on columns
How to trim newlineChar,blanks on columns [message #205397] Fri, 24 November 2006 08:48 Go to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
Hi,
I use SQL Loader to load some data. But 'TRAILING NULLCOLS' is not very effective sometimes for the last column.

I get newlineCharacters,blanks for one of the column in the Oracle DB.

1) Can anyone suggest how to improve SQLLoader to trim newLine character,blanks.
My ctl file is as below:

LOAD DATA
REPLACE
INTO TABLE ALO1
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ORDER_TYPE POSITION(1) "decode(:ORDERNo,null,'$',:ORDERNO)",
...
..
ABC "decode(:ABC,null,'$',:ABC)"
)
Sample file:
ABCD|40|ZS04||AM02|IN

2) How to manually trim newLinecharacters,blanks on the column in a table? I used trim as below but of no avail.
update ALO1 set ABC= trim(ABC)

Please advice

Regards,
Sham.
Re: How to trim newlineChar,blanks on columns [message #205404 is a reply to message #205397] Fri, 24 November 2006 09:17 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

ABC CHAR NULLIF(ABC=BLANKS)
Previous Topic: question on data pump
Next Topic: How to create .sh script in Linux for Export Operation
Goto Forum:
  


Current Time: Wed Dec 07 12:22:19 CST 2016

Total time taken to generate the page: 0.10996 seconds