Sql-server – sqlcmd returning literal ‘NULL’ for null columns when outputting to CSV

sql serversql-server-2008-r2sqlcmd

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:

USE [OSCAR_UK]
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.

Best Answer

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.

Related Question