17

I was working trying to get this bcp tool to work in a particular way. The -c switch is supposed to export using chars, but for some reason there was a weird char showing in Notepad++ like if it was UNICODE or some other formatting.

I wanted to get that char, which was an Empty string in the database, to export as an empty string into the text file. How do you do that?

mimoralea
  • 9,590
  • 7
  • 58
  • 59
  • Similar question [Why does bcp output null when the column contains an empty string and empty string when the column is null?](http://stackoverflow.com/questions/1644731/why-does-bcp-output-null-when-the-column-contains-an-empty-string-and-empty-stri). – Michał Powaga Apr 17 '13 at 06:56

2 Answers2

28

Well, after looking around the web this is what I found. Thought to put it in SO so more people have access to it.

http://www.techtalkz.com/microsoft-sql-server/147106-how-export-empty-strings-via-bcp.html

But when I run bcp, the empty strings are written to the text file using the ASCII "NUL" character (0x0). Instead, I want the field to be filled with blanks (0x20).

Solution:

In a text file, an empty string is interpreted as NULL when you bulk in data. The NUL character is interpreted as the empty string, so there is consistency. But that does not help much if you are exporting data to another system....

Simply wrap around a NULLIF that field that is possibly empty in the database and make it catch '' and replace with NULL. That will get converted into ^^values^^^^more values^^and more^^ so you get double separator with an blank instead of the NUL character.

E.g.:

...
NULLIF(pri.InstanceName, '') as PerformanceInstanceName,
...

Hope this helps someone.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
mimoralea
  • 9,590
  • 7
  • 58
  • 59
1

Just a follow-up...

The NULLIF solution above works fine in Microsoft SQL, but for a more standardized solution that works in almost any Database you should try COALESCE.

The syntax is very similar:

COALESCE(pri.InstanceName, '') as PerformanceInstanceName
skuntsel
  • 11,624
  • 11
  • 44
  • 67
D3Young
  • 25
  • 1
  • 8
  • 1
    Actually, COALESCE is the opposite of what's needed in this case. We need to identify nulls and replace with a ''' – Ralph Trickey Mar 15 '16 at 17:15
  • that's what i thought, but when i changed from `coalesce` to `nullif` they null output was replaced by an empty string – Jeff Nov 02 '17 at 15:01