Specifying Field Conditions

A field condition is similar to the condition in the CONTINUEIF clause, with two important differences. First, positions in the field condition refer to the logical record, not to the physical record. Second, you may specify either a position in the logical record or the name of a field that is being loaded.

where:

start	Specifies the starting position of the comparison field in the logical record.
end	Specifies the ending position of the comparison field in the logical record. Either start:end or start-end is acceptable. If you omit end, the length of the field is determined by the length of the comparison string. If the lengths are different, the shorter field is padded: character strings are padded with blanks, hexadecimal strings are padded with zeroes.
column_name	The name of a column in the database table. If column_name is used instead of start:end, then the specification for that column defines the comparison field. Column_name must match exactly the name of the column in the table's database definition. Use quotation marks around the column name if it is a SQL or SQL*Loader keyword, contains special characters, or is case sensitive. For more information, see "Specifying Filenames and Database Objects" .
operator	A comparison operator for either equal or not equal:

				= 	!=	 ¬=	<> 

'char string'	A string of characters enclosed within single or double quotation marks that is compared to the comparison field. If the comparison is true, then this row is inserted into the table.
X'hex string'	A byte string in hexadecimal format used in the same way as the character string above.
BLANKS	A keyword denoting an arbitrary number of blanks, described next.

Comparing Fields to BLANKS

For example, use the following clause to load a blank field as null:

column_name ... NULLIF column_name=BLANKS 

The BLANKS keyword only recognizes blanks, not tabs. It can be used in place of a literal string in any field comparison. The condition is TRUE whenever the column is entirely blank.

The BLANKS keyword also works for fixed-length fields. Using it is the same as specifying an appropriately-sized literal string of blanks. For example, the following specifications are equivalent:

fixed_field CHAR(2) NULLIF (fixed_field)=BLANKS 

fixed_field CHAR(2) NULLIF (fixed_field)="  " 

Note: There can be more than one "blank" in a multi-byte character set. It is a good idea to use the BLANKS keyword with these character sets instead of specifying a string of blank characters. The character string will match only a specific sequence of blank characters, while the BLANKS keyword will match combinations of different blank characters. For more information on multi-byte character sets, see .

Comparing Fields to Literals

NULLIF (1:4)="_" 

compares the data in position 1:4 with 4 blanks. If position 1:4 contains 4 blanks, then the clause evaluates as true.

Hexadecimal strings are padded with hexadecimal zeroes. The clause

NULLIF (1:4)=X'FF' 

compares position 1:4 to hex 'FF000000'.