Why does bcp output null when the column contains an empty string and empty string when the column is null?

sql sql-server null bcp
David Hayes picture David Hayes · Oct 29, 2009 · Viewed 10.6k times · Source

This struck me as really weird behaviour and I spent a while checking for bugs in my code before I found this

"out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string." (from http://msdn.microsoft.com/en-us/library/ms162802.aspx)

Obviously this allowed me to fix my problem but can anybody think of or does anybody know a reason why this is the case?

Answer

gbn picture gbn · Nov 1, 2009

It's been some time, but I'm sure it's a backwards compatibility/legacy back to SQL Server 6.5

SQL Server 6.5 could not store empty string: there was always one space. This changed with SQL 7

So '' -> NULL and ' ' -> '' is correct from an ancient history perspective.

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More