問題描述
以下代碼片段應該展示了我在 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 WorldSQL Server 連接以 nul 字符結尾的字符串就好了.看這個例子:
SELECT len('"Hello World' + CHAR(0) + '"')輸出:
14SELECT 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.
這篇關于無法連接到末尾帶有 CHAR(0) 的字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！