主页 / dba / 问题 / 197263
Accepted
Krismorte
Asked: 2018-02-08 04:36:09 +0800 CST

用错误字符整理问题

  • 772

好吧，这个问题是众所周知的，但如果有的话，我正在寻找更聪明的解决方案。

出于某种原因，系统无法识别某些字符，我无法比较列

在此处输入图像描述

下面是一个文本示例：

正确的

ASPIRADOR ULTRASSONICO-LOCAÇAO (NOTA FISCAL SERVIÇO)

错误的

ASPIRADOR ULTRASSONICO-LOCA€AO（非财政服务）

实际上我正在通过这个功能解决这个问题

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

sql-server t-sql

1 个回答

  1. Best Answer

    这是一个不正确的编码问题。字符编码为 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 个。

    • 10

相关问题