如何使Microsoft BCP导出空字符串而不是NUL字符? [英] How to make Microsoft BCP export empty string instead of a NUL char?

问题描述

我正在尝试使此bcp工具以特定方式工作. -c开关应该使用chars导出，但是由于某些原因，Notepad ++中显示了一个奇怪的char，就像它是UNICODE还是其他格式一样.

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.

我想获取该char(作为数据库中的空字符串)作为空字符串导出到文本文件中.你怎么做到的?

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?

推荐答案

好吧，在网上浏览后，这就是我发现的东西.考虑将其放入SO，以便更多人可以使用它.

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

但是当我运行bcp时，空字符串将被写入文本文件 使用ASCII"NUL"字符(0x0).相反，我希望该领域是 充满空白(0x20).

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).

解决方案:

在文本文件中，批量时，空字符串被解释为NULL 在数据中. NUL字符被解释为空字符串，因此 有一致性.但这对导出没有太大帮助 数据传输到另一个系统....

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....

只需在数据库中可能为空的NULLIF周围换行，并使其捕获''并替换为NULL.它将被转换成^^ values ^^^^更多的值^^和更多^^，因此您将获得一个带空格而不是NUL字符的双分隔符.

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.

例如:

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

希望这对某人有帮助.

这篇关于如何使Microsoft BCP导出空字符串而不是NUL字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！