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.