Delimiter option

Specifies the field delimiter.

The column delimiter is always expressed by using Latin-9 encoding. Thus, the delimiter is always a single byte.

Technically, the column delimiter can be any character or value within the following range:

hex     dec     octal
        ---     ---     -----
        x01     001     <001>
   to   xFF     255     <377>

For the column delimiter, observe the following restrictions and limitations:

It cannot be <NUL>.
For example, binary <000>.
It cannot be a number.
For example, 0 to 9.
It cannot match the delimiter that is used as one of the following values:
- The record delimiter
  Generally, a <NL>.
- The date delimiter
- The decimal delimiter
- The time delimiter

Tip:

If you are loading UTF8 data into NCHAR or NVARCHAR columns, limit your delimiter to something within the following range.

hex     dec     octal
        ---     ---     -----
        x01     001     <001>
   to   x7F     127     <177>

The default is the pipe character ( '|'), except for the nzload command: for the command, the default is '\t' (tab). This option is not supported for the fixed-length format.

The system processes an input row by identifying the successive fields within that row. A single-character field delimiter separates adjacent fields. The lack of a field delimiter between fields is an error. You can use a trailing field delimiter that follows the last field in a row, but it is not necessary.

You can specify the following types of delimiters:

Numeric: 0xNN or NN, where NN is a number for either hexadecimal or decimal.
Control characters: ^A - ^Z (low-order 5 bits) and ^a - ^z (low-order 5 bits).
Symbols: \b (backspace; ASCII value 8), \t (horizontal tab; ASCII value 9), \n (line feed; ASCII value 10), \f (form feed; ASCII value 12), \r (carriage return; ASCII value 13), \\ (backslash), \' (quotation mark), \" (double quotation mark).
Literal: Any character, such as c (the noncontrol character c).

You can specify characters in the 7-bit ASCII range by using either a quoted value (for example, Delimiter '|') or its unquoted decimal number (for example, Delimiter 124). To specify a byte value higher than 127, use the decimal number.

To use a character other than a 7-bit ASCII character as a delimiter, specify it as a decimal or hexadecimal number. Do not specify a character literal, which can result in errors from encoding transformation. For example, to use the hexadecimal value 0xe9 as a delimiter (which is é in Latin9), use Delimiter 0xe9. Do not use Delimiter 'é'.

Although the system accepts alphanumeric characters, to avoid ambiguity, do not select a delimiter that conflicts with the data in a field. If you also use the DateDelim, TimeDelim, DateTimeDelim, or MeridianDelim option, select different delimiters for each option.

When you use the nzload command, you can enter escape characters on the command line, such as \b. The default for the nzload command is '\t' (tab). If you use the CREATE EXTERNAL TABLE command, the only special character that you can specify is \t or "\t".