TERMINATED_BY

Default: comma

Purpose

The TERMINATED_BY parameter specifies a field terminator that overrides the default.

Syntax and Description

TERMINATED_BY=['char'| x'hex-char' | WHITESPACE]

The field terminator must be a single character. It can be in hexadecimal notation. It can also be whitespace. If TERMINATED_BY=WHITESPACE is specified, then data is read until the first occurrence of a whitespace character (spaces, tabs, blanks, line feeds, form feeds, or carriage returns). Then the current position is advanced until no more adjacent whitespace characters are found. This allows field values to be delimited by varying amounts of whitespace.

If you use TERMINATED_BY=WHITESPACE, then null fields cannot contain just blanks or other whitespace because the blanks and whitespace will be skipped and an error may be reported. This means that if you have null fields in the data, then you may have to use another string to indicate the null field and use the NULLIF parameter to indicate the NULLIF string. For example, you could use the string "NoData" to indicate a null field and then insert the string "NoData" in the data to indicate a null field. Specify NULLIF="NoData" to tell SQL*Loader to set fields with the string "NoData" to NULL.

Restrictions

  • None

Example

In the following example, fields are terminated by the | character.

> sqlldr hr TABLE=employees TERMINATED_BY="|"