blocks|key|4232804|text|这已经有一段时间了，但我确信这是对SQL+Server+6.5的向后兼容/遗留|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4232805|SQL+Server+6.5无法存储空字符串:始终有一个空格。这种情况在SQL+7中发生了变化|4232806|因此，从古代历史的角度来看，''+->+NULL和'+'+->+''是正确的。|offset|length|style|CODE|4232807|entityMap^0|0|0|E|A|P|9|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@$F|O|G|P|H|I]|$F|Q|G|R|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

It's been some time, but I'm sure it's a backwards compatibility/legacy back to SQL Server 6.5

SQL Server 6.5 could not store empty string: there was always one space. This changed with SQL 7

So <code>'' -&gt; NULL</code> and <code>' ' -&gt; ''</code> is correct from an ancient history perspective.

blocks|key|240673|text|SELECT+ARTICULO+as+Articulo,+
++++++++case+when+rtrim(CCOLOR)+=+''+then+null+else+rtrim(CCOLOR)+end+as+Color,+
++++++++case+when+rtrim(TALLE)+=+''+then+null+else+rtrim(TALLE)+end+as+Talle,
from+precios|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|240674|发送null代替empty。|unstyled|offset|length|style|CODE|240675|我在这里找到了最佳解决方案：|240676|https://bytes.com/topic/sql-server/answers/143738-bcp-inserting-blank-space-empty-string|240677|我找到了一个解决办法，在|240678|查询中使用Case结构将空字符串更改为null。BCP反过来将结果null输出为空！谢谢你的帮助。Eric|blockquote|240679|240680|entityMap|0|LINK|mutability|MUTABLE|url^0|0|2|4|0|0|0|2G|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|12|8|@$G|13|H|14|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|15|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|16|8|@]|9|@$G|17|H|18|1|19]]|A|$]]|$1|O|3|P|5|F|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1B|8|@]|9|@]|A|$]]|$1|T|3|-4|5|F|7|1C|8|@]|9|@]|A|$]]|$1|U|3|-4|5|F|7|1D|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|N]]]]

<pre><code>SELECT ARTICULO as Articulo, 
 case when rtrim(CCOLOR) = '' then null else rtrim(CCOLOR) end as Color, 
 case when rtrim(TALLE) = '' then null else rtrim(TALLE) end as Talle,
from precios
</code></pre>

Send <code>null</code> in place of empty.

I find the best solution here:

<a href="https://bytes.com/topic/sql-server/answers/143738-bcp-inserting-blank-space-empty-string" rel="nofollow noreferrer">https://bytes.com/topic/sql-server/answers/143738-bcp-inserting-blank-space-empty-string</a>

<blockquote>
 I found a work around, using a Case structure in the sql query to
 change empty string to a null. The BCP in turn outputs the resulting
 null as an empty!
 Thanks for your assistance.
 Eric
</blockquote>

blocks|key|2808083|text|这与BCP的“默认值”部分相关：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2808084|https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server|offset|length|2808085|2808086|例如，如果数据文件中有null字段，则改为加载该列的默认值。|blockquote|2808087|2808088|2808089|您必须回想一下从其他奇怪的系统导入纯文本文件的日子。BCP将''转换为“未定义”(=缺失数据)，并在数据库中设置空值(=缺失数据)。对于其他系统，NULL+from数据库的另一种方式必须是''。|style|CODE|2808090|要从数据库中获取“真实”数据，请使用-k开关：|2808091|https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server#keep_nulls|2808092|2808093|以下限定符指定数据文件中的空字段在大容量导入操作期间保留其NULL值，而不是继承表列的默认值(如果有)。|2808094|2808095|2808096|然后您的文件/数据库中就有了ASCII+0x0。|2808097|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|3Q|0|0|0|0|0|0|U|2|2M|2|0|I|2|0|0|41|1|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@$D|1A|E|1B|1|1C]]|A|$]]|$1|F|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|1E|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1H|8|@$D|1I|E|1J|N|O]|$D|1K|E|1L|N|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1M|8|@$D|1N|E|1O|N|O]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1P|8|@]|9|@$D|1Q|E|1R|1|1S]]|A|$]]|$1|T|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|U|3|V|5|I|7|1U|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1X|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|C]]|17|$5|13|14|15|A|$16|S]]]]

This is related to the "default values" section for BCP:

<a href="https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server</a>

<blockquote>
 For example, if there is a null field in a data file, the default value for the column is loaded instead.
</blockquote>

You have to think back to days where importing plain text files from other weird systems. BCP translates <code>''</code> as "not defined"(=missing data) and sets a NULL in the database (=missing data). The other way around a NULL from database must be a <code>''</code> for the other systems.

To get the 'real' data out of the database use the <code>-k</code> switch:

<a href="https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server#keep_nulls" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server#keep_nulls</a>

<blockquote>
 The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns.
</blockquote>

Then you have your ASCII 0x0 in your file/database.

This struck me as really weird behaviour and I spent a while checking for bugs in my code before I found this

"out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string." (from <a href="http://msdn.microsoft.com/en-us/library/ms162802.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms162802.aspx</a>)

Obviously this allowed me to fix my problem but can anybody think of or does anybody know a reason why this is the case?

Why does bcp output null when the column contains an empty string and empty string when the column is null?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

这让我感到非常奇怪的行为，我花了一段时间检查代码中的bug，然后发现了以下内容out从数据库表或视图复制到文件。如果指定现有文件，该文件将被覆盖。提取数据时，请注意bcp实用程序将空字符串表示为null，将空字符串表示为空字符串。(来自)显然，这让我解决了我的问题，但有谁能想到或者知道为什么会出现这种情况？

问为什么bcp在列包含空字符串时输出null，当列为null时输出空字符串？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么bcp在列包含空字符串时输出null，当列为null时输出空字符串？EN