> I have a table that I use BCP to dump it to a file, the file must be
> PIPE delimited so I am using the -t "|" parameter to make the file pip
> delimited. I am having the problem that whatever empty fields the table
> has, when bcp is used to create the file it inserts a blank in between
> the pipes.
> For example, if I have :
> field1 = "abc"
> field2 = ''
> field3 = 'def'
>
> after doing the bcp the file will look as in:
>
> abc| |def
>
> instead of:
>
> abc||def
>
> how do I get rid of this space int he file ?

> mdscorp (l***@mds-corp.com) writes:
> > I have a table that I use BCP to dump it to a file, the file must be
> > PIPE delimited so I am using the -t "|" parameter to make the file pip
> > delimited. I am having the problem that whatever empty fields the table
> > has, when bcp is used to create the file it inserts a blank in between
> > the pipes.
> > For example, if I have :
> > field1 = "abc"
> > field2 = ''
> > field3 = 'def'
> >
> > after doing the bcp the file will look as in:
> >
> > abc| |def
> >
> > instead of:
> >
> > abc||def
> >
> > how do I get rid of this space int he file ?
>
> Define a view over the table with definitions like:
>
>    field2 = nullif(field2, '')
>
> A NULL value will be extracted as a zero-length field. (And this is why
> zero-length column gets extracted as a single space: so that it would
> not be taken as a NULL value when you bulk-load the file.)
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx