用錯誤字元整理問題

好吧，這個問題是眾所周知的，但如果有的話，我正在尋找更聰明的解決方案。

出於某種原因，系統無法辨識某些字元，我無法比較列

下面是一個文本範例：

對

超聲波吸塵器-租賃（服務發票）

錯誤的

超聲波吸塵器-租賃（服務發票）

實際上我正在通過這個功能解決這個問題

create function fixcollation(@ps_Texto VARCHAR(4000)) returns VARCHAR(4000) 

as 

begin  

   declare @vlgsv1itu INT declare @nxn68ezzi INT declare @dw17rsyva  VARCHAR(50) declare @iw8a2z01i VARCHAR(50) declare @t64e98xq6 VARCHAR(50) declare @zwjs2imy3 INT declare @jsyt85sy8 VARCHAR(4000)  

   ---------------------------------------------------- 

   set @dw17rsyva = ' …ƃ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãå♣—–éëꚇ€§' 
   set @iw8a2z01i = 'áàãâäÁÀÃÂÄéèêëÈÉÊËíìïÍÌÏóòõôöÓÒÕÔÖúùûüÚÙÛÜçǺØ' 
   set @jsyt85sy8 = @ps_Texto set @zwjs2imy3 = IsNull(datalength(@ps_Texto), 0) 
   set @nxn68ezzi = 1 
   while(@nxn68ezzi <= IsNull(datalength( @ps_Texto), 0)) 

   begin 

       set @vlgsv1itu = 1 

       while(@vlgsv1itu <= IsNull(datalength(@dw17rsyva), 0)) 
       begin 

           IF(ASCII(SUBSTRING(@ps_Texto, @nxn68ezzi, 1) COLLATE LATIN1_GENERAL_CS_AS) = ASCII(SUBSTRING(@dw17rsyva, @vlgsv1itu, 1) COLLATE LATIN1_GENERAL_CS_AS)) 
           BEGIN 
               set @t64e98xq6 = SUBSTRING( @iw8a2z01i, @vlgsv1itu, 1) set @jsyt85sy8 = SUBSTRING(@jsyt85sy8, 1, @nxn68ezzi -1) + @t64e98xq6 + SUBSTRING(@jsyt85sy8, @nxn68ezzi + 1, @zwjs2imy3 -  @nxn68ezzi) 
               break 
           end 
           set @vlgsv1itu = @vlgsv1itu + 1 
       end 
       set @nxn68ezzi = @nxn68ezzi + 1 
   end 
   return @jsyt85sy8 
end 

所以，我的問題是：這是最好的方法還是我在這裡錯過了什麼？

編輯

只是一個補充測試

select dbo.fixcollation(' …ƃ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãå♣—–éëꚇ€§')
select dbo.FixCodePage850toCodePage1252(' …ƃ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãå♣—–éëꚇ€§')

這是我的生產環境中的結果

固定整理

修復CodePage850到CodePage1252

我個人感謝 Solomon Rutzky

這是一個不正確的編碼問題。字元編碼為 DOS 程式碼頁 850，但您使用的目標程式碼頁（基於Latin1_General排序規則）是 Windows 程式碼頁 1252。例如，在 DOS 程式碼頁 850 中，Ç字元的值為 0x80（或 128十進制）。但是，Windows 程式碼頁 1252 中的相同值 0x80 為您提供€. 同樣，Ã在 DOS 程式碼頁中，850 的值為 0xC7（或十進制的 199）。但是，Windows 程式碼頁 1252 中相同的 0xC7 值會為您提供Ç.

不正確的字元是不正確的，因為導入到 SQL Server 時為源指定了錯誤的編碼。這不會在 SQL Server 中發生，因為這將是程式碼頁轉換問題，在這種情況下，相同的“字元”將針對目標程式碼頁中的相同字元翻譯其值（如果該字元存在於目標程式碼頁中，否則你會得到?）。例如：

SELECT ASCII('Ç' COLLATE Latin1_General_CI_AS) AS [CP1252 Value],
      'Ç' COLLATE SQL_Latin1_General_CP850_CI_AS AS [CharacterInCP850],
      ASCII('Ç' COLLATE SQL_Latin1_General_CP850_CI_AS) AS [CP850 Value];

回報：

CP1252 Value     CharacterInCP850     CP850 Value
199              Ç                    128

意思是，這很可能發生在文件導入期間 - BCP.exe、SQLCMD.exe、BULK INSERT、OPENROWSET(BULK...)、讀取文件的自定義應用程式碼等 - 其中要麼指定了錯誤的原始碼頁，要麼根本沒有程式碼頁為源指定。如果正在執行為此文件指定程式碼頁 1252 的導入，它將具有您在此處看到的效果，因為這些字節是針對程式碼頁 850 而不是程式碼頁 1252 編碼的。

應該注意的是，如果驅動程序（ODBC 等）被告知使用錯誤的程式碼頁，來自應用程式碼的數據也可能發生這種情況。

現在，關於解決此問題的方法：

1. 理想情況下，將更新/修復導入數據的方法，以正確說明數據已編碼的實際程式碼頁。
2. 如果無法修復導入過程，那麼其他公司提供的功能不是最好的方法。事實上，它可能是最慢、最複雜的方法，也容易出錯（如果它們沒有映射所有字元）。沒有理由在將字元成對載入到表變數中時進行兩個循環，這將允許使用該函式SUBSTRING進行單個循環。REPLACE並且使用該ASCII函式和區分大小寫、區分重音的排序規則是不必要的，並且在使用_BIN2排序規則時容易出錯（如果兩個字元匹配正在搜尋的內容）會更好。
3. 使用以下函式進行轉換。首先它獲取目前字元串的字節，然後將這些字節注入到VARCHAR使用程式碼頁 850 的列中，然後從表變數中選擇該值到一個局部變數中（無論如何都需要返回該值），其效果是將字元串轉換為數據庫預設排序規則使用的程式碼頁（此處必須是程式碼頁 1252，否則您將無法從函式中獲取“正確”字元串）：

USE [tempdb];
GO
CREATE FUNCTION dbo.FixCodePage850toCodePage1252
(
   @CodePage850String VARCHAR(8000)
)
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @Convert850to1252 TABLE
 (
   [String] VARCHAR(8000) COLLATE SQL_Latin1_General_CP850_CI_AS
 );
 DECLARE @ReturnValue VARCHAR(8000);

 INSERT INTO @Convert850to1252 ([String])
 VALUES (CONVERT(VARBINARY(8000), @CodePage850String, 0));

 SELECT @ReturnValue = [String] -- automatic conversion to Code Page of database
 FROM @Convert850to1252;

 RETURN @ReturnValue;
END;
GO

測試這兩個函式返回相同的結果：

SELECT dbo.fixcollation('lj§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

SELECT dbo.FixCodePage850toCodePage1252('lj§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

---

我想出了一個測試來檢查所有字元的映射，以防提供翻譯功能的公司錯過任何映射。我過濾掉了只能在 Code Page 850 中找到的圖形字元和無點“i”。

USE [tempdb];
GO
;WITH nums AS
(
   SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
   FROM   master.sys.columns
), vals AS
(
 SELECT nums.[num] AS [Value],
        CHAR(nums.[num]) AS [Character],
        dbo.fixcollation(CHAR(nums.[num])) AS [OldWay],
        dbo.FixCodePage850toCodePage1252(CHAR(nums.[num])) AS [NewWay]
 FROM   nums
)
SELECT vals.*,
      ASCII(vals.[NewWay]) AS [NewValue]
FROM   vals
WHERE  vals.[Character] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[OldWay] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[Value] NOT IN (176, 177, 178, 179, 180, 185, 186, 187, 188,
                           191, 192, 193, 194, 195, 196, 197, 200, 201,
                           202, 203, 204, 205, 206, 217, 218, 219, 220,
                           223, 254, 213); -- characters only in CP850

這將返回一個包含 52 個字元的列表，這些字元可能通過導入過程像其他字元一樣被誤譯，但被 UDF 跳過，您是由另一家公司提供的，該公司僅處理明顯 98 個可能字元中的 46 個。

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