I'm using sqlcmd (SQL Server 2008 R2) to execute a simple query to output data to a .csv file. In the SQL file, I have the following code:

SELECT DISTINCT ISNULL(LTRIM(RTRIM(il.[LifeInsuredFirstName])),'') AS [First Name]
  ,ISNULL(LTRIM(RTRIM(il.[LifeInsuredSurname])),'') AS [Last Name]
  ,CONVERT(varchar(26),il.[DateOfBirth],23) AS [Date Of Birth]
  ,ISNULL(LTRIM(RTRIM(il.[Gender])),'') AS [Gender]
  ,ISNULL(LTRIM(RTRIM(il.[PostCode])),'') AS [Postcode]
  ,ISNULL(LTRIM(RTRIM(ce.[PolicyNumber])),'') AS [Policy Number]
FROM [dbo].[Insured_List] il
LEFT OUTER JOIN [dbo].[Cession] ce
ON il.[pk_Cession] = ce.[pk_Cession]

My problem is that any field containing a NULL value is returned to the .csv file as the string literal 'NULL' rather than an empty string, despite my use of ISNULL to trap that condition.

My searches so far have really only found people complaining that it all worked OK in SQL Server 2005. Have I missed something simple? If anyone could point me towards a research source that would be very kind.

Thanks in advance.

EDIT: It appears that the problem occurs only when the output is redirected to a file, not if it is allowed to simply appear on-screen. I'll have to do some sort of global replace after the fact. It's a pain but if that's the way it is, it's the way it is.

  • 1
    If you get words "NULL" in the output CSV despite the fact that you are using ISNULL to trap the nulls and replace them with empty string, then the only possible explanation for me is that you have those "NULL" words in the source. That, or this is not the query that actually produces the NULLs. – Andriy M Jul 12 '16 at 16:45
  • @Andriy M - looking at the source data in SQL server, the affected items all show NULL on a yellow background, indicating that the value is in fact null rather than a value of 'NULL'. Also, a SQL query specifying WHERE [Column Name] = 'NULL' returns no rows. This is why I'm mystified. – Spratty Jul 13 '16 at 8:05
  • What do you mean "redirected to a file"? – Paweł Tajs Jul 13 '16 at 9:59
  • @Paweł Tajs - the output from the SQL query is not displayed on the screen but written, comma-delimited, to a file. – Spratty Jul 13 '16 at 10:46
  • Confirming the same behavior; definitely adds a lot to the file size of sparse result sets – Will Ayd Feb 10 '20 at 21:34

I cannot reproduce the problem you're seeing.

I'm using a SQL Server 2008 R2 instance, connecting to it via sqlcmd.exe using the following test command, which I've wrapped for readability:

.\sqlcmd -S "My2008R2Instance" -E -d tempdb -Q "set nocount on; 
   create table #t (col1 varchar(10) null, col2 varchar(10) null);
   insert into #t(col1, col2) values (null, null);
   select coalesce(col1, '') as col1, coalesce(col2, '') as col2 
   from #t;" -o C:\temp\test.csv -s "," -h -1

The output consists of a single line file with a comma between the two columns:


As you can see, there are no 'NULL' values returned; they've been replaced by the zero-length-string, as required by the COALESCE function.

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.