無法從 NVARCHAR 列中刪除 char 0x0000

所以，我知道所有關於 Replace 函式和 char(0) 的錯誤。

我有一列 ( ) 包含來自錯誤導入的NVARCHAR(128)一些字元。NCHAR(0x0000)

我正在使用 SQL Server 2008 R2。

該列的排序規則是：SQL_Latin1_General_CP1_CI_AS。

我已經在網上嘗試了所有我可能找到的東西，但沒有任何東西會從列中取出臭氣熏天的 char(0) 字元。

這是我的最新嘗試，結果令人費解（sql server 中的錯誤？）。

我有一個循環遍歷每個字元並用特定字元替換 0x0000 的函式。

ALTER FUNCTION dbo.ReplaceCharZero
(
 @testString NVARCHAR(MAX),
 @charToReplaceWith NCHAR(1) = ' '
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
   DECLARE
       @i INT = 1 ,
       @fixedString NVARCHAR(MAX) = ''
   WHILE @i <= LEN(@testString)
       BEGIN
           IF SUBSTRING(@testString, @i, 1) = CHAR(0x00)
               BEGIN
                   --PRINT 'Found' + CAST(@i AS VARCHAR)
                   SET @fixedString = @fixedString + @charToReplaceWith         
               END
           ELSE
               BEGIN
                   --PRINT 'NOT Found' + CAST(@i AS VARCHAR)
                   SET @fixedString = @fixedString
                       + SUBSTRING(@testString, @i, 1)
               END
           SET @i = @i + 1
       END
   RETURN @fixedString               
END

這就是我要做的測試：

BEGIN TRAN

   DECLARE @ShortDescription NVARCHAR(128), @SupplierId INT, @Language CHAR(2)

   SELECT TOP 1 @ShortDescription = ShortDescription,
                @SupplierId = SupplierID,
                @Language = Language
   FROM   Supplier_Multilingual
   WHERE  ShortDescription LIKE '%' +  CHAR(0x00) + '%'

   SET @ShortDescription =  REPLACE(dbo.ReplaceCharZero(@ShortDescription, ' '), '-', ' ')

   UPDATE dbo.Supplier_MultiLingual
   SET    ShortDescription = NULL 
   WHERE  SupplierID = @SupplierId
   AND    Language = @Language

   UPDATE dbo.Supplier_MultiLingual
   SET    ShortDescription = dbo.ReplaceCharZero(@ShortDescription, '') 
   WHERE  SupplierID = @SupplierId
   AND    Language = @Language

   SELECT *
   FROM   Supplier_Multilingual
   WHERE  SupplierId = @SupplierId
   AND    Language = @Language
   AND    ShortDescription LIKE '%' +  CHAR(0x00) + '%'

ROLLBACK TRAN

在我的測試中，我將該列作為一個變數，我在其上執行我的函式以去除0x0000，然後我用 a 更新原始列NULL，然後我將它更新為我的固定變數，然後我執行一個查詢來查看是否0x0000字元仍然存在，它們確實存在。

CHAR(0)似乎已轉換為空格，或CHAR(32).

下面展示了這個問題：

DECLARE @Data NVARCHAR(255);
SELECT @Data = 'this is a test' + CHAR(0) + 'of null';
DECLARE @i INT;
SET @i = 1;
DECLARE @txt NVARCHAR(255);
WHILE @i < LEN(@Data)
BEGIN
   IF SUBSTRING(@Data, @i, 1) = CHAR(0) 
   BEGIN
       SET @txt = 'found a null char at position ' + CONVERT(NVARCHAR(255),@i);        
       RAISERROR (@txt, 0, 1) WITH NOWAIT;
   END
   SET @i = @i + 1;
END

函式中的錯誤REPLACE？還是在一般的 SQL Server 中？我不太確定。這裡唯一的“問題”僅僅是不完全理解字元串比較是如何處理的。

排序規則定義了某些字元將如何與其他字元進行比較。有時有關於某些字元組合等同於一個或多個其他字元的規則。並且有一些關於0x00（空）和0x20（空格）等字元彼此相等或其他字元相等的規則。VARCHAR而且，為了讓生活更有趣，使用 SQL Server 排序規則（即以 開頭）的數據有一些特定的細微差別SQL_，如以下範例所示：

SELECT REPLACE('VARCHAR with SQL_Latin1_General_CP1_CI_AS'+CHAR(0)+'Matches', CHAR(0),
              ': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE(N'NVARCHAR with SQL_Latin1_General_CP1_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
              N': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE('VARCHAR with Latin1_General_100_CI_AS'+CHAR(0)+'Matches', CHAR(0),
              ': ' COLLATE Latin1_General_100_CI_AS);
SELECT REPLACE(N'NVARCHAR with Latin1_General_100_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
              N': ' COLLATE Latin1_General_100_CI_AS);

回報：

VARCHAR with SQL_Latin1_General_CP1_CI_AS: Matches

NVARCHAR with SQL_Latin1_General_CP1_CI_AS

VARCHAR with Latin1_General_100_CI_AS

NVARCHAR with Latin1_General_100_CI_AS

---

因此，讓我們使用基於@Max答案中的查詢的查詢來看看這種行為。我N在字元串文字和 中添加了前綴CHAR(0)，並且我還添加了一個額外的前綴，NCHAR(0)只是為了讓下一部分更容易看到。我添加了查詢以顯示正在使用的實際程式碼點（以證明這些0x0000值確實存在，並呼叫以REPLACE()查看其中是否確實存在錯誤）。

DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

SELECT @Data;
SELECT CONVERT(VARBINARY(50), @Data);
SELECT REPLACE(@Data, NCHAR(0), N'~');

這是

0x7400680069007300200069007300 0000 610020007400650073007400 0000 6F00660020006E0075006C006C00

這是

第一個結果顯示字元串由於(null)終止而在“is”之後結束。第二個結果顯示了底層程式碼，我強調了0x0000字元的兩個實例。第三個結果表明該REPLACE函式似乎與傳入的0x0000字元不匹配。NCHAR(0)

但我們是否應該期望這NCHAR(0)會在這裡匹配？我們可以通過強制二進制排序規則有效地禁用通常應用於字元串比較的所有等價規則。我們將使用_BIN2排序規則，因為_BIN排序規則已被棄用，除非您對它們有特定需求，否則不應使用它們。

將以下查詢添加到上面的集合併重新執行批處理。

SELECT REPLACE(@Data, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~');

您應該得到以下附加結果：

這是~一個測試~null

因此該REPLACE功能確實有效，並且在 SQL Server 2008 R2、SP3 以及 SQL Server 2012 SP2 上都進行了測試。

---

好的，所以這只是解決了REPLACE不使用的問題NCHAR(0)，但沒有解決NCHAR(0)等同於空格（即NCHAR(32)或NCHAR(0x20)）的問題。

現在我們將使用來自@Max 答案的主查詢的改編。我再次在測試字元串中添加了一個額外NCHAR(0)的內容（實際上只是用它替換了位置 8 的空格），並將匹配字元的程式碼點添加到RAISERROR消息中。

SET NOCOUNT ON;
GO
DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

DECLARE @i INT,
       @CodePoint INT;
SET @i = 1;

WHILE @i < LEN(@Data)
BEGIN
   IF SUBSTRING(@Data, @i, 1) = NCHAR(0) --COLLATE Latin1_General_100_BIN2
   BEGIN
       SET @CodePoint = UNICODE(SUBSTRING(@Data, @i, 1));
       RAISERROR (N'Found a NULL char (Code Point = %d) at position: %d',
                  10, 1, @CodePoint, @i) WITH NOWAIT;
   END;
   SET @i = @i + 1;
END;

此查詢（該COLLATE子句仍被註釋掉）將返回：

Found a NULL char (Code Point = 32) at position: 5
Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 32) at position: 10
Found a NULL char (Code Point = 0) at position: 15
Found a NULL char (Code Point = 32) at position: 18

這些是@Max 測試中報告的相同位置，但現在它顯示了在每種情況下它匹配的程式碼點。是的，它等同於32和0。

現在，取消註釋該COLLATE子句並重新執行它。它將返回：

Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 0) at position: 15

另一種不使用COLLATE子句的方法是將IF語句更改為：

IF ( UNICODE(SUBSTRING(@Data, @i, 1)) = 0 )

當然，這兩個修復（WHILE使用COLLATE子句或UNICODE()函式的循環）都不需要解決0x0000從輸入數據中刪除字元的原始問題，因為簡單REPLACE（使用COLLATE子句）可以處理這個問題。

---

概括：

• 如果您想替換/刪除字元串中的字元，則無需使用循環。REPLACE只要您_BIN2通過關鍵字為 3 個輸入參數中的至少一個指定排序規則，該函式就可以正常工作COLLATE（從技術上講，哪種二進制排序規則並不重要，因為二進制排序規則只比較數字程式碼點值）。
• 如果需要測試特定的程式碼點（例如在上面顯示的循環中），那麼使用該UNICODE()函式可能是最快的，因為它只報告實際存在的值。這應該比使用COLLATE關鍵字更快，因為這需要做更多的工作。
• 如果需要測試一系列程式碼點/字元，則使用COLLATE關鍵字，指定_BIN2二進制排序規則。

引用自：https://dba.stackexchange.com/questions/82094