bcp is inserting blank space for empty string

I'm doing a bcp out of a table to a file. Some of the fields in a
record may have an empty string.

When I bcp out to the file and examine it, the fields that have an
empty string in the database now show up in the file as having one
blank character.

Why is bcp doing this? I don't want the blank character in my output.

Thanks,
Eric

re: bcp is inserting blank space for empty string

Which version of MSSQL? What data type is the column? Is ANSI_PADDING
on or off for the column? If your column is char(1), then this would be
expected, as char columns are padded out with spaces; a varchar should
not be padded, though.

If this doesn't help, I suggest you post (simplified) CREATE TABLE and
INSERT statements to show exactly what your table and data look like.

Simon

re: bcp is inserting blank space for empty string

Simon Hayes wrote:[color=blue]
> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
> on or off for the column? If your column is char(1), then this would[/color]
be[color=blue]
> expected, as char columns are padded out with spaces; a varchar[/color]
should[color=blue]
> not be padded, though.
>
> If this doesn't help, I suggest you post (simplified) CREATE TABLE[/color]
and[color=blue]
> INSERT statements to show exactly what your table and data look like.
>
> Simon[/color]

SQL Server 2000
Varchar 16
ANSI_PADDING is off.

Give it a shot. Create a table with three columns, all varchar. Insert
an empty string into each to get rid of any nulls. Then do a bcp out to
an output file and let me know if you get the same results.

Eric

re: bcp is inserting blank space for empty string

"epaetz" <epaetz41@hotmail.com> wrote in message
news:1112704850.296614.123580@o13g2000cwo.googlegr oups.com...[color=blue]
>
> Simon Hayes wrote:[color=green]
>> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
>> on or off for the column? If your column is char(1), then this would[/color]
> be[color=green]
>> expected, as char columns are padded out with spaces; a varchar[/color]
> should[color=green]
>> not be padded, though.
>>
>> If this doesn't help, I suggest you post (simplified) CREATE TABLE[/color]
> and[color=green]
>> INSERT statements to show exactly what your table and data look like.
>>
>> Simon[/color]
>
> SQL Server 2000
> Varchar 16
> ANSI_PADDING is off.
>
> Give it a shot. Create a table with three columns, all varchar. Insert
> an empty string into each to get rid of any nulls. Then do a bcp out to
> an output file and let me know if you get the same results.
>
> Eric
>[/color]

I used this test script (on 8.00.760 Enterprise):

set ansi_padding off
go
create table eric (col1 varchar(16) null)
go
insert into eric select '' -- empty string
insert into eric select ' ' -- single space
go
select col1, len(col1) as 'Length', datalength(col1) as 'Datalength', col1 +
'X'
from eric
go

Then I exported the file with bcp:

bcp Development..eric out c:\temp\eric.txt -S kilkenny -c -T

When I checked eric.txt with a hex editor, it showed this:

00 0D 0A 20 0D 0A

So the empty string is an ASCII NUL character in this case, but the space is
ASCII 20. Is this the behaviour you see, or do you get something different?
Setting ANSI_PADDING ON didn't change the output (and BOL recommends it
should always be on anyway).

Simon

re: bcp is inserting blank space for empty string

I found a work around, using a Case structure in the sql query to
change empty string to a null. The BCP in turn outputs the resulting
null as an empty!

Thanks for your assistance.

Eric