Choose terminators with patterns that do not appear in any of the data.
For example, using a tab terminator with a string of data that also contains a tab creates an ambiguity: which tab represents the end of the string? bcp always looks for the first possible terminator, which in this case would be incorrect, since the first tab it would encounter would be the one that is part of the data string.
Data in native format can also conflict with terminators. Given a column that contains a 4-byte integer in native format, if the values of these integers are not strictly limited, it will be impossible to choose a terminator that is guaranteed not to appear inside the data. Use bcp’s native format option for data in native format.
“No
terminator” is different from a “null terminator,” which
is an invisible, but real, character.
A field terminator string can be up
to 30 characters long. The most common terminators are a tab (entered
as \t and used for
all columns except the last one), and a newline (entered as \n and
used for the last field in a row). Other terminators are: \0 (the
null terminator), \ (backslash), and \r (Return).
When choosing a terminator, be sure that its pattern does not appear
in any of your character data, because bcp always
looks for the first possible terminator.
For example, if you used tab terminators with a string that contained a tab, bcp would not be able to identify which tab represents the end of the string. bcp always looks for the first possible terminator, so, in this example it would find the wrong one.
A terminator or prefix affects the actual length of data transferred:
When a terminator or prefix is present, it affects the length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is immediately followed by the terminator or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from Adaptive Server already padded to the full length).
When bcp is copying in from a file, data is transferred until either the number of bytes indicated in the “Length” prompt has been copied or the terminator is encountered. Once the number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed.
Fields stored as char (except char, nchar, and binary fields) instead of their database datatypes take less file storage space with the default length and prefix or a terminator. bcp can use either a terminator or a prefix to determine the most efficient use of storage space. bcp suggests the maximum amount of storage space required for each field as the default. For char or varchar data, bcp accepts any length.
Table 3-6 and Table 3-7 show the interaction of prefix lengths, terminators, and field length on the information in the file. “P” indicates the prefix in the stored table; “T” indicates the terminator; and dashes, (--) show appended spaces. An ellipsis (…) indicates that the pattern repeats for each field. The field length is 8 bytes for each column; “string” represents the 6-character field each time.
Prefix length = 0 |
Prefix length–1, 2, or 4 |
|
|---|---|---|
No terminator |
string--string--... |
Pstring--Pstring--... |
Terminator |
string--Tstring--T... |
Pstring--TPstring--T... |
Prefix length = 0 |
Prefix length–1, 2, or 4 |
|
|---|---|---|
No terminator |
string--string--... |
PstringPstring... |
Terminator |
stringTstringT... |
PstringTPstringT... |
