By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

bcp is inserting blank space for empty string

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a

Simon Hayes wrote:
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


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

Jul 23 '05 #3

P: n/a

"epaetz" <ep******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Simon Hayes wrote:
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


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


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
Jul 23 '05 #4

P: n/a
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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.