0

Is there a way to prevent SSMS 2012 from adding the word 'NULL' to CSV exports?

Steps to reproduce behavior:

  • Create a new query (Ctrl+N)
  • Select Query | Results To | Results to Grid (Ctrl+D)
  • Run query: SELECT getdate() NOW, NULL DUMMY; results in one row and two columns, the second column displays ‘NULL'
  • Right click the results pane and choose Save Results As…; save the file as CSV
  • Open the file with Notepad (view the ‘raw’ values)

Actual result:

NOW,DUMMY
2015-10-16 10:25:28.403,NULL

Desired result:

NOW,DUMMY
2015-10-16 10:25:28.403,

Is there a setting that will produce the desire result? Do newer versions of SSMS work differently in this regard?

  • SELECT getdate() NOW, '' DUMMY? Just don't select NULL, select an empty string instead. – dwjv Oct 16 '15 at 14:44
  • The query generates a null to *simulate' behavior in 'real' queries; I wanted to demonstrate how nulls are exports to CSV files. – craig Oct 16 '15 at 14:47
1

You could use COALESCE(NullableColumn,'') for any column you don't want to export with NULL values.

That COALESCE will output zero-length strings instead.

Most of the time, however, it is important to know the difference between a zero-length string and a NULL value in exported data.

You're sample query would become:

SELECT getdate() NOW, COALESCE(NULL,'') DUMMY;

Although, I'd really prefer to see it as:

SELECT [Now] = getdate()
    , Dummy = COALESCE(NULL,'');

Putting the column alias at the beginning of the line makes it easier to find columns referenced in the output when doing debugging work down the road.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.