I have a database that I am exporting data from. Once it is in text format I run the dos sort command, then finally compress it. Everything was going well till I noticed that my dump files had lines that were being split in two
I started looking at the original file and found it contained Ascii character 0 or Null. So my first response was to try isnull(column,’-‘) in the query, but that didn’t work
I checked the lookup tables and found that a join was matching and returning an empty string.
On reading the BCP manual 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.
So nulls are converted to empty strings and empty strings are converted to nulls? Who thought this was a good idea?
So I changed my sql to be NULLIF(column,”) and now everyone is happy.
