INF: BCP and NULL Values

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

A number of questions often arise concerning the BCP utility and NULL values when working with fixed-length character data files. This article addresses these questions.

MORE INFORMATION

There is an inherent problem with representing the value NULL, because there is no standard ASCII or character representation for NULL.

Consider the following cases, in which "SQL" represents the database server, and "FILE" represents the input or output BCP data file.

FIXED-LENGTH CHARACTER BCP, ANY FIELD EXCEPT THE LAST

FILE     SQL
----     ---

spaces   -> spaces (CHAR NULL col.)
spaces   <- CHAR NULL

spaces   -> 0 (INT NULL col.)
spaces   <- INT NULL

In the second case, you may wonder why CHAR NULL BCPs out as spaces. Again, this is because of the lack of a standard character representation for NULL. The string "NULL" couldn't be properly written to the output file, because this would be a valid string, and upon input be loaded as the string "NULL," not the value NULL. Also, because this is fixed-length BCP, the column width must be preserved, using spaces as the most practical character.

For the third case, note spaces in the input data file are loaded as 0 in the SQL table. This is not because of BCP: remember, this is character-mode BCP of ASCII data into an INT column, which means the character data must be converted to INT. The server CONVERT function translates spaces to 0, which is proper behavior. Although it may seem intuitively that spaces should be translated to NULL, remember there is no standard representation for NULL, and hence no basis for expecting spaces to be converted to NULL.

In the fourth case, INT NULL BCPs out as spaces for reasons similar to the second case.

FIXED-LENGTH CHARACTER BCP, LAST FIELD IN FILE

FILE     SQL
----     ---

spaces   -> spaces (CHAR NULL col.)

spaces   -> 0 (INT NULL col.)

All six of the above cases assume an end-of-line terminator is used.

For the first and fourth above cases, note that spaces in the input data file are loaded as spaces and 0, respectively, for reasons previously discussed.

In the second and fifth above cases, note that NULL is BCPed out as nothing. That is, just an end-of-line terminator is placed in the output file immediately after the preceding field.

In the third and sixth above cases, note that it is possible to BCP in a NULL using fixed-length character BCP. This is because the involved field is the last, or rightmost one in the in data file. Why NULL is loaded in this case can best be seen by examining the only non-ambiguous way to BCP in NULL values. This is by using fields with explicit delimiters, not by using non-delimited fixed-length fields as in the above examples. With explicit delimiters, it becomes possible to place two adjacent delimiters in the input file, signifying "no data." Similarly, in the above case involving the last field of a fixed-length character BCP input data file, the presence of an explicit end-of-line terminator, or delimiter, makes it possible to distinguish this field as NULL.

The only non-ambiguous way to represent NULL in character-mode BCP is by using two adjacent delimiters in a character-delimited file. Manipulation of the source file to achieve this state is possible via several techniques. Alternatively, if 0 or spaces in the destination SQL column has no valid meaning for the particular database, you can change it to NULL via a bulk UPDATE statement following the BCP in.