bcp out empty string into csv files

[Jay]

I have a SQL Server table with nvarchar type column which has not null
constraint. I am inserting empty string ("") from Java to the table
column. When I export this table into .csv file using bcp tool, empty
string gets written as NUL character. Is there any way I can bcp out
empty string as empty string itself instead of NUL to the file?

The bcp command I used to export table into csv file is

bcp "SELECT skillID,profile ID,skillName,ac tive,dateInacti ve from
db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CR SSQL -t"," -
T

In the table skillName column can have empty string value.
Corresponding to the empty string csv file contain 'NUL' stored in it.
I do not want 'NUL'. I need empty string in the resulting file.
Is there any way it can be done?

Thanks
Jayaraj

[Sybaseguru]

Re: bcp out empty string into csv files

Have you tried using isnull() in the select?

Jay wrote:

I have a SQL Server table with nvarchar type column which has not null
constraint. I am inserting empty string ("") from Java to the table
column. When I export this table into .csv file using bcp tool, empty
string gets written as NUL character. Is there any way I can bcp out
empty string as empty string itself instead of NUL to the file?
>
The bcp command I used to export table into csv file is
>
bcp "SELECT skillID,profile ID,skillName,ac tive,dateInacti ve from
db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CR SSQL -t"," -
T
>
In the table skillName column can have empty string value.
Corresponding to the empty string csv file contain 'NUL' stored in it.
I do not want 'NUL'. I need empty string in the resulting file.
Is there any way it can be done?
>
Thanks
Jayaraj

[Erland Sommarskog]

Re: bcp out empty string into csv files

Jay (rkjayaraj@gmai l.com) writes:

I have a SQL Server table with nvarchar type column which has not null
constraint. I am inserting empty string ("") from Java to the table
column. When I export this table into .csv file using bcp tool, empty
string gets written as NUL character. Is there any way I can bcp out
empty string as empty string itself instead of NUL to the file?
>
The bcp command I used to export table into csv file is
>
bcp "SELECT skillID,profile ID,skillName,ac tive,dateInacti ve from
db_cra..Skill" queryout C:\Skill.csv -w -S 10.76.253.70\CR SSQL -t"," -
T
>
In the table skillName column can have empty string value.
Corresponding to the empty string csv file contain 'NUL' stored in it.
I do not want 'NUL'. I need empty string in the resulting file.
Is there any way it can be done?

I once filed a bug for this, but had it closed as by design. The issue
is that when you bulk-load a file, an empty field is taken as NULL, so
they need a way to import the empty string.

Since you are using queryout, there is an easy way out:
nullif(skillNam e, ''). This will also give NULL also for a string of spaces
only. If you want to retain trailing spaces, you need to use

CASE WHEN datalength(skil lName) 0 THEN skillName ELSE NULL END

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx