无法连接到末尾带有 CHAR(0) 的字符串

以下代码片段应该展示了我在 SQL Server 和我的 VBScript Web 应用程序中观察到的一个问题:

SELECT '"Hello World"'SELECT '"Hello World' + CHAR(0) + '"'

结果:

你好世界"你好，世界

请注意，第二个结果行缺少最后的双引号.

我知道您不能在 SQL Server 中将字符串连接到 NULL 值.但是，以下代码段表明，就 SQL Server 而言，CHAR(0) 不被视为 NULL.

SELECT ISNULL(CHAR(0), 'CHAR(0) 被认为是 NULL')

结果:

<预><代码>

此外，当我从 DB 中将此值读入 VBScript 中的变量时，我也无法连接到该变量.

我不是 C 开发人员，但我知道 C 风格的字符串以空字符结尾.这是否相关?有人可以帮助我理解为什么在 SQL Server 和 VBScript 中会发生这种情况吗?

我可以通过简单地替换违规数据中的所有 CHAR(0) 来解决我的直接问题，但首先我想了解这样做的原因并制定预防性解决方案.

<小时>

包括一些 VBScript

testSql = "SELECT '""Hello World' + CHAR(0) + '""' AS TestString"设置结果集 = conn.execute(testSql)testString = resultSet.Fields.Item("TestString")testString = testString &}"Response.Write 测试字符串

结果:

"Hello World

解决方案

SQL Server 连接以 nul 字符结尾的字符串就好了.看这个例子:

SELECT len('"Hello World' + CHAR(0) + '"')

输出:

14

SELECT len('"Hello World' + CHAR(0) + '"' + '"Hello World' + CHAR(0) + '"')

输出:

28

当您先将字符串存储到 CTE 或表中时，结果是一样的.

它在 VB 中的处理和输出使它看起来好像没有.尝试打印您在 VB 中从 SQL 中得到的字符串的长度.

The following snippets should demonstrate an issue I've observed in both SQL Server and in my VBScript web application:

SELECT '"Hello World"'
SELECT '"Hello World' + CHAR(0) + '"'

Results:

"Hello World"
"Hello World

Notice that the second result line is missing the final double quote.

I understand that you can't concatenate strings to a NULL value in SQL Server. However, the following snippet reveals that CHAR(0) is not considered NULL as far as SQL Server is concerned.

SELECT ISNULL(CHAR(0), 'CHAR(0) IS CONSIDERED NULL')

Result:

Also, when I read this value from the DB into a variable in VBScript, I'm unable to concatenate to that variable as well.

I'm not a C developer, but I understand that C-style strings are terminated by the null character. Is this relevant? Can someone please help me understand why this is happening in SQL Server and in VBScript?

I can solve my immediate problem by simply replacing all CHAR(0)'s in the offending data, but first I'd like to understand the reason for this and develop a preventative solution.

---

EDIT: Including some VBScript

testSql = "SELECT '""Hello World' + CHAR(0) + '""' AS TestString"
set resultSet = conn.execute(testSql)
testString = resultSet.Fields.Item("TestString")

testString = testString & "}"

Response.Write testString

Result:

"Hello World

解决方案

SQL server concatenates strings ending in nul characters just fine. See this example:

SELECT len('"Hello World' + CHAR(0) + '"')

Output:

14

SELECT len('"Hello World' + CHAR(0) + '"' + '"Hello World' + CHAR(0) + '"')

Output:

28

The result is the same when you store the string into a CTE or table first.

Its the handling and output in VB that makes it appear as if it does not. Try to print the length of the string your are getting out of SQL in VB.