trimming white space & blanks

Hi,

I am loading data through sqlldr direct=true. My data fileds are terminated by some control character like '^^'. I want both leading & trailing blanks removed in character fields.

Is it possible through control file? If yes, how? By default, sqlldr does not do.

kailash pareek

Is it always the same character? If so, find the hex value for that char and you can use it as a delimiter in your .ctl file. You would use something like:
fields terminated by x'12' ...

i believe you can use the "WHEN" parameter to specify what characters you want ignored.

Any administration book should have info on this.

- Magnus

Hi,

Thanks for your reply, but that is not what I want. I am using terminated by clause. My question is a value ^^ aaa ^^ is being loaded as one having two blanks on either side.

I want to trim from both sides and can not used functions as I am using direct=true, neither I want to do that by some post load processing logic.

Is there is sqlloader itself something which I am missing?

Kailash pareek

You can apply the rtrim(ltrim()) function within your sqlldr control file.

Quote:

---

Originally posted by marist89
You can apply the rtrim(ltrim()) function within your sqlldr control file.

---

For example:
fields terminated by x'12' optionally enclosed by x'15' (
field1 "rtrim(ltrim(:field1))",
...

Hi,

Thanks for ltrim/rtrim. My data is millions of rows and I am using DIRECT=TRUE. The sql operators are not allowed in direct mode.

Any more suggestions ?

kailash pareek

Don't use direct=true