What is the ASCII Equivalent to the SQL 2000 NULL?

Chr(0) would have been my first guess but it does not import into SQL 2000
as <NULL>.

Chr(20) is what SQL 2000 gives you when you export a NULL to a CSV file, but
SQL 2000 does not import Chr(20) as <NULL>.

So someone please tell me what does import from a CSV file to SQL 2000 as a
<NULL> value?

"Jamie Carper" <JamieCarper (AT) discussions (DOT) microsoft.com> wrote

field1,field2,,field4 (field3 is null)

And I make sure there's no default for the field on an INSERT.

The alternative would be an ActiveX script on the transform for the column.
Test for some particular value in the source and set the destination to null
if it matches. I use something like this for fixed field files, where there
are no column delimiters. I translate blanks in a numeric field to zero,
but it could as easily be null.
Jack Peacock

I am using the alternative you spoke of. Which is why I have posed the
question.

When I use Replace(xxxxxx, Chr(46), Chr(0)), if the type I am transforming
to is an integer, I get the NULL value. However if I the field is a char of
any type I get some unknown character. I then have to run a separate SQL
UPDATE statement to find this mystery character and replace it with a NULL.

Is there more than one type of NULL for SQL?

"Jack Peacock" wrote:

"Jamie Carper" <JamieCarper (AT) discussions (DOT) microsoft.com> wrote

ASCII NUL (one L, not two) character is not the same thing. If you store a
NUL character in a column that's a legitimate value, and there are
applications which do require the ability to store binary zeroes in a char
string (for instance graphics or ESC sequences for old style CRT terminals).
What you did was replace one character with another, no different than if
you had replaced Chr(46) with Chr(32), a space. That's why there is a
separate keyword for NULL...it does not have an ASCII equivalent.

Think of it as the difference between a bottle with a vacuum inside (NUL
char) or no bottle at all (SQL NULL).

If you are trying to transform a field that is a binary value instead of
ASCII digits then you have to do some tricks. First convert each byte into
a number using CHR, then add up according to the numeric type. I often
convert from packed decimal (BCD), where each 4 bits in a binary string is a
separate digit. Same problem, extract a byte at a time, separate the digits
by dividing, and add up the results. I can provide the ActiveX script for
an example if it would be helpful.
Jack Peacock