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

Question

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 1

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.

Answer 2

SELECT ARTICULO as Articulo, 
        case when rtrim(CCOLOR) = '' then null else rtrim(CCOLOR) end as Color, 
        case when rtrim(TALLE) = '' then null else rtrim(TALLE) end as Talle,
from precios

Send null in place of empty.

I find the best solution here:

https://bytes.com/topic/sql-server/answers/143738-bcp-inserting-blank-space-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