SQL Server - 将 varchar 转换为另一个排序规则(代码页)以修复字符编码
我正在查询使用 SQL_Latin1_General_CP850_BIN2 归类的 SQL Server 数据库.表行之一有一个 varchar，其值包含 +/- 字符(Windows-1252 代码页中的十进制代码 177).
I'm querying a SQL Server database that uses the SQL_Latin1_General_CP850_BIN2 collation. One of the table rows has a varchar with a value that includes the +/- character (decimal code 177 in the Windows-1252 codepage).
当我直接在 SQL Server Management Studio 中查询表时，我在该行中得到一个乱码字符而不是 +/- 字符.当我将此表用作 SSIS 包中的源时，目标表(使用典型的 SQL_Latin1_General_CP1_CI_AS 排序规则)以正确的 +/- 字符结尾.
When I query the table directly in SQL Server Management Studio, I get a gibberish character instead of the +/- character in this row. When I use this table as the source in an SSIS package, the destination table (which uses the typical SQL_Latin1_General_CP1_CI_AS collation), ends up with the correct +/- character.
我现在必须建立一种机制，无需 SSIS 即可直接查询源表.我如何才能获得正确的字符而不是胡言乱语?我的猜测是我需要将列转换/转换为 SQL_Latin1_General_CP1_CI_AS 排序规则，但这不起作用，因为我不断收到乱码.
I now have to build a mechanism that directly queries the source table without SSIS. How do I do this in a way that I get the correct character instead of gibberish? My guess would be that I would need to convert/cast the column to the SQL_Latin1_General_CP1_CI_AS collation but that isn't working as I keep getting a gibberish character.
我尝试了以下方法但没有成功:
I've tried the following with no luck:
select
columnName collate SQL_Latin1_General_CP1_CI_AS
from tableName
select
cast (columnName as varchar(100)) collate SQL_Latin1_General_CP1_CI_AS
from tableName
select
convert (varchar, columnName) collate SQL_Latin1_General_CP1_CI_AS
from tableName
我做错了什么?
推荐答案
字符集转换在数据库连接级别隐式完成.您可以使用参数Auto Translate=False"在 ODBC 或 ADODB 连接字符串中强制关闭自动转换.不推荐这样做.请参阅:https://msdn.microsoft.com/en-us/library/ms130822.aspx
Character set conversion is done implicitly on the database connection level. You can force automatic conversion off in the ODBC or ADODB connection string with the parameter "Auto Translate=False". This is NOT recommended. See: https://msdn.microsoft.com/en-us/library/ms130822.aspx
当数据库和客户端代码页不匹配时，SQL Server 2005 中存在代码页不兼容.https://support.microsoft.com/kb/KbView/904803
There has been a codepage incompatibility in SQL Server 2005 when Database and Client codepage did not match. https://support.microsoft.com/kb/KbView/904803
SQL-Management Console 2008 及更高版本是一个 UNICODE 应用程序.输入或请求的所有值都在应用程序级别被解释为这样.与列排序规则之间的对话是隐式完成的.您可以通过以下方式验证:
SQL-Management Console 2008 and upwards is a UNICODE application. All values entered or requested are interpreted as such on the application level. Conversation to and from the column collation is done implicitly. You can verify this with:
SELECT CAST(N'±' as varbinary(10)) AS Result
这将返回 0xB100，它是 Unicode 字符 U+00B1(在管理控制台窗口中输入).您不能关闭 Management Studio 的自动翻译".
This will return 0xB100 which is the Unicode character U+00B1 (as entered in the Management Console window). You cannot turn off "Auto Translate" for Management Studio.
如果您在选择中指定不同的排序规则，只要自动翻译"仍处于活动状态，您最终会进行双重转换(可能会丢失数据).在选择过程中，原始字符首先转换为新的排序规则，然后自动转换"到正确的"应用程序代码页.这就是为什么您的各种 COLLATION 测试仍然显示相同的结果.
If you specify a different collation in the select, you eventually end up in a double conversion (with possible data loss) as long as "Auto Translate" is still active. The original character is first transformed to the new collation during the select, which in turn gets "Auto Translated" to the "proper" application codepage. That's why your various COLLATION tests still show all the same result.
如果您将结果转换为 VARBINARY 而不是 VARCHAR，您可以验证指定排序规则在选择中是否有效，因此 SQL Server 转换不会失效由客户在呈现之前:
You can verify that specifying the collation DOES have an effect in the select, if you cast the result as VARBINARY instead of VARCHAR so the SQL Server transformation is not invalidated by the client before it is presented:
SELECT cast(columnName COLLATE SQL_Latin1_General_CP850_BIN2 as varbinary(10)) from tableName
SELECT cast(columnName COLLATE SQL_Latin1_General_CP1_CI_AS as varbinary(10)) from tableName
如果 columnName 只包含字符 '±'
This will get you 0xF1 or 0xB1 respectively if columnName contains just the character '±'
如果您使用的字体没有提供正确的字形，您仍然可能会得到正确的结果，但会得到错误的字符.
You still might get the correct result and yet a wrong character, if the font you are using does not provide the proper glyph.
请通过在适当的样本上将查询转换为 VARBINARY 来仔细检查您的字符的实际内部表示，并验证此代码是否确实对应于定义的数据库排序规则 SQL_Latin1_General_CP850_BIN2
Please double check the actual internal representation of your character by casting the query to VARBINARY on a proper sample and verify whether this code indeed corresponds to the defined database collation SQL_Latin1_General_CP850_BIN2
SELECT CAST(columnName as varbinary(10)) from tableName
只要转换始终以相同的方式进出，应用程序整理和数据库整理中的差异可能不会被注意到.一旦您添加具有不同排序规则的客户端，就会出现问题.然后你可能会发现内部转换无法正确匹配字符.
Differences in application collation and database collation might go unnoticed as long as the conversion is always done the same way in and out. Troubles emerge as soon as you add a client with a different collation. Then you might find that the internal conversion is unable to match the characters correctly.
说了这么多，您应该记住，在解释结果集时，Management Studio 通常不是最终参考.即使它在 MS 中看起来很乱，它仍然可能是正确的输出.问题是这些记录是否在您的应用程序中正确显示.
All that said, you should keep in mind that Management Studio usually is not the final reference when interpreting result sets. Even if it looks gibberish in MS, it still might be the correct output. The question is whether the records show up correctly in your applications.
相关文章