how to tell sqlldr to trim trailing and leading whitespace

removing-whitespace sql-loader
Isaac Kleinman picture Isaac Kleinman · Dec 19, 2011 · Viewed 40.4k times · Source

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

?

Answer

KD007 picture KD007 · Dec 27, 2016

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.

Related questions

Remove all whitespace in a string

I want to eliminate all the whitespace from a string, on both ends, and in between words. I have this Python code: def my_handle(self): sentence = ' hello apple ' sentence.strip() But that only eliminates the whitespace on …

Read More
Efficient way to remove ALL whitespace from String?

I'm calling a REST API and am receiving an XML response back. It returns a list of a workspace names, and I'm writing a quick IsExistingWorkspace() method. Since all workspaces consist of contiguous characters with no whitespace, I'm assuming the …

Read More
How can I trim leading and trailing white space?

I am having some troubles with leading and trailing white space in a data.frame. For example, I like to take a look at a specific row in a data.frame based on a certain condition: > myDummy[myDummy$country == …

Read More