在 t-sql 中转换 nvarchar 变量的排序规则
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1631936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cast collation of nvarchar variables in t-sql
提问by Sandor Davidhazi
I need to change the collation of an nvarchar variable. By documentation:
我需要更改 nvarchar 变量的排序规则。通过文档：
(...) 3. The COLLATE clause can be specified at several levels. These include the following:
Casting the collation of an expression. You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column. For the collation of an expression, see Collation Precedence(Transact-SQL).
(...) 3. 可以在多个级别指定 COLLATE 子句。这些包括以下内容：
转换表达式的排序规则。您可以使用 COLLATE 子句将字符表达式应用于特定排序规则。字符文字和变量被分配了当前数据库的默认排序规则。列引用被分配了列的定义排序规则。有关表达式的排序规则，请参阅排序规则优先级(Transact-SQL)。
However I can't figure out the correct syntax for the usage of CAST(), CONVERT() or variable declaration with DECLARE for this purpose.
但是，我无法弄清楚为此目的使用 CAST()、CONVERT() 或带有 DECLARE 的变量声明的正确语法。
回答by Lukasz Lysik
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
回答by nalply
CASTor CONVERTis superfluous!
CAST或者CONVERT是多余的！
SELECT N'abc' COLLATE French_CS_AS
It is superfluous because just changing the collation does not change the data type NVARCHAR.
这是多余的，因为仅更改排序规则不会更改数据类型NVARCHAR。
回答by Robert Livermore
If you are changing between 2 and 1 byte, or vice-ver-sa, character encodings then CAST or Convert is necessary. It is not superfluous in these cases.
如果您在 2 和 1 个字节之间更改字符编码，反之亦然，则需要 CAST 或 Convert。在这些情况下，这并不是多余的。
When the source column is a 2 byte character sequence (nchar, nvarchar) and the selection projection is required to be a single byte character (char, varchar), you should specify the cast and convert. Apply the collation conversion before the casting between the type systems.
当源列是 2 字节字符序列（nchar、nvarchar）并且选择投影要求是单字节字符（char、varchar）时，您应该指定强制转换和转换。在类型系统之间进行转换之前应用排序规则转换。
SELECT CAST(N'ФBC' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(10)) AS ProjectedSingleByte
回答by Xavier_prash
If you would like to compare or join two columns of different collation this could help. In my case I had to compare two columns with one using 'SQL_Latin1_General_CP1_CI_AS' and the other using 'Latin1_General_CP1_CI_AS'.
如果您想比较或连接不同排序规则的两列，这可能会有所帮助。在我的情况下，我不得不使用“SQL_Latin1_General_CP1_CI_AS”比较两列，另一列使用“Latin1_General_CP1_CI_AS”。
I simply used this option where i join these two.
我只是在我加入这两个时使用了这个选项。
on A.Person = B.NAME collate database_default
在 A.Person = B.NAME 上整理 database_default
