How To Output Data In CSV Format If Fields Have NULLs?

Question

question

David 2 1 asked Feb 1, '10

How To Output Data In CSV Format If Fields Have NULLs?

I know this can be done using DTS however I would like to know how I can output data in csv format using a query if the columns contain nulls?

For example, if you create and insert into the following table then try to output to a comma seperated string the values get truncated:

create table testcol(
col001 varchar(10),
col002 varchar(10),
col003 varchar(10))

insert into testcol values('A','B','C')
insert into testcol values('A','B',NULL)
insert into testcol values('A',NULL,'C')
insert into testcol values(NULL,'B','C')
insert into testcol values('A',NULL,NULL)

select col001 +','+ col002 +',' col003
from testcol

col003

A,B,
A,B,
NULL
NULL
NULL

Thanks as always.

t-sql sql-server-2000

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-01T10:23:40Z

graz answered Feb 1, '10

You can use COALESCE to replace NULLs with another value. So something like this should work:

select COALESCE(col001, '') +','+ COALESCE(col002,'') +',' + COALESCE(col003, '') from testcol

This replaces the NULLs with an empty string.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-01T12:29:22Z

TG answered Feb 1, '10

You can use BCP to output CSVs. This link has the BCP reference as well as examples: http://msdn.microsoft.com/en-us/library/ms162802.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question