trimming white space & blanks

[kkpareek]

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

[marist89]

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' ...

[jgmagnus]

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

Any administration book should have info on this.

- Magnus

[kkpareek]

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

[marist89]

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

[marist89]

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))",
...

[kkpareek]

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

[marist89]

Don't use direct=true