Trimming Whitespace

Blanks, tabs, and other nonprinting characters (such as carriage returns and line feeds) constitute whitespace. Leading whitespace occurs at the beginning of a field. Trailing whitespace occurs at the end of a field. Depending on how the field is specified, whitespace may or may not be included when the field is inserted into the database. This is illustrated in Figure 10-1, where two CHAR fields are defined for a data record.

The field specifications are contained in the control file. The control file CHAR specification is not the same as the database CHAR specification. A data field defined as CHAR in the control file simply tells SQL*Loader how to create the row insert. The data could then be inserted into a CHAR, VARCHAR2, NCHAR, NVARCHAR2, or even a NUMBER or DATE column in the database, with the Oracle database handling any necessary conversions.

By default, SQL*Loader removes trailing spaces from CHAR data before passing it to the database. So, in Figure 10-1, both Field 1 and Field 2 are passed to the database as 3-byte fields. However, when the data is inserted into the table, there is a difference.

Figure 10-1 Example of Field Conversion

Description of "Figure 10-1 Example of Field Conversion "

Column 1 is defined in the database as a fixed-length CHAR column of length 5. So the data (aaa) is left-justified in that column, which remains 5 bytes wide. The extra space on the right is padded with blanks. Column 2, however, is defined as a varying-length field with a maximum length of 5 bytes. The data for that column (bbb) is left-justified as well, but the length remains 3 bytes.

Table 10-5 summarizes when and how whitespace is removed from input data fields when PRESERVE BLANKS is not specified. See "How the PRESERVE BLANKS Option Affects Whitespace Trimming" for details on how to prevent whitespace trimming.

Table 10-5 Behavior Summary for Trimming Whitespace

Specification	Data	Result	Leading Whitespace Present(1)	Trailing Whitespace Present1
Predetermined size	__aa__	__aa	Yes	No
Terminated	__aa__,	__aa__	Yes	Yes²
Enclosed	"__aa__"	__aa__	Yes	Yes
Terminated and enclosed	"__aa__",	__aa__	Yes	Yes
Optional enclosure (present)	"__aa__",	__aa__	Yes	Yes
Optional enclosure (absent)	__aa__,	aa__	No	Yes
Previous field terminated by whitespace	__aa__	aa	No	³

The rest of this section discusses the following topics with regard to trimming whitespace:

¹ When an all-blank field is trimmed, its value is NULL.

Except for fields that are terminated by whitespace.

Presence of trailing whitespace depends on the current field's specification, as shown by the other entries in the table.