how to tell sqlldr to trim trailing and leading whitespace

Question

I'm using sqlldr to load data in which some of the fields have trailing and leading whitespaces. Is there a way to to tell sqlldr to ignore these whitespaces other than by saying

field "trim(:field)"

?

Solution 1

field "trim(:field)" works fine in most of the case but i came up with typical case where the column size was char(1) but the data in control file was of ' Y' and "trim(:field)" failed to load the data. After a lot of research i came to know that trim() function trims the white spaces from the data but at the same time adds null to data in place of white spaces so the data length in above example will be 2 as it counts null values on the left side of data. Thus, the data will be some thing like nullY since, the column size in table is less than the actual size its not loaded and oracle throws error message. To overcome this issue i used: "trim(null from trim(:field))"

Above script first trims the white spaces and then again trim the null appended by the sql-loader.

I hope information i have provided will be helpful to anyone facing the problem as i did. I could not resist to post this as i did not find any thread answering this sort of issue.

Solution 2

I know it's an old thread, but I'll chime in anyway.

As with many things, the answer is "it depends." It depends whether the data is fixed format or delimited. If delimited, it also depends whether the control files uses the OPTIONALLY ENCLOSED BY clause. For all the sordid details, see the Oracle SQL*Loader documentation. For example, try this link: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1007768

Comments

[Isaac Kleinman]

In fact, by default, without the optional PRESERVE BLANKS, leading and trailing whitespace will be ignored. My question arose from the fact that I was using a tool on top of sqlldr which generates a ctl file which adds the PRESERVE BLANKS by default.

[Mark F Guerra]

As it turns out, leading and trailing whitespace areignored by default, but only for certain data types. However some types such as VARCHAR will leave the spaces intact. See the link below csee.umbc.edu/portal/help/oracle8/server.815/a67792/…

[Reimius]

I'm curious though, it looks like it doesn't work if you test it by doing select trim(null from trim(' food')) from dual. Does it work differently in sqlloader?