UDF中的COLLATE无法正常工作 [英] COLLATE in UDF does not work as expected

问题描述

我有一个带有文本字段的表格。我想选择所有大写文字所在的行。这段代码可以正常工作，并返回 ABC ：

I have a table with text field. I want to select rows where text is in all caps. This code works as it should, and returns ABC:

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
txt COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(txt)

然后我创建UDF（如建议在此处）：

then I create UDF (as suggested here):

CREATE FUNCTION [dbo].[fnsConvert]
(
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
)
RETURNS NVARCHAR(2000)
AS
    BEGIN
        IF ( @c = 'SQL_Latin1_General_CP1_CS_AS' )
            SET @p = @p COLLATE SQL_Latin1_General_CP1_CS_AS
        RETURN @p    
    END

并运行如下所示（对我来说似乎是等效代码）：

and run it as follows (which looks like an equivalent code to me):

SELECT txt
FROM (SELECT 'ABC' AS txt UNION SELECT 'cdf') t
WHERE 
dbo.fnsConvert(txt, 'SQL_Latin1_General_CP1_CS_AS') = UPPER(txt)

但是，它返回 ABC 以及 cdf 。

为什么会这样，如何使它工作？

Why is that so, and how do I get this to work?

PS我需要这里的UDF才能调用case- .Net LINQ2SQL提供程序的敏感比较。

PS I need UDF here to be able to call case-sensitive comparison from .Net LINQ2SQL provider.

推荐答案

变量不能具有自己的归类。它将始终使用服务器的默认设置。检查以下内容：

A variable cannot have it's own collation. It will always use the server's default. Check this:

-我声明了三个变量，每个变量都有自己的归类-至少有人会这样认为：

--I declare three variables, each of which get's its own collation - at least one might think so:

DECLARE @deflt VARCHAR(100) = 'aBc'; --Latin1_General_CI_AS in my system
DECLARE @Arab VARCHAR(100) = 'aBc' COLLATE Arabic_100_CS_AS_WS_SC;
DECLARE @Rom VARCHAR(100) = 'aBc' COLLATE Romanian_CI_AI

-现在检查这个。这三个变量都被视为系统的默认排序规则：

--Now check this. All three variables are seen as the system's default collation:

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT @deflt AS Deflt, @Arab AS Arab, @Rom AS Rom'
                                          ,N'@deflt varchar(100), @Arab varchar(100),@Rom varchar(100)'
                                          ,0);

/*
name    system_type_name    collation_name
Deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Latin1_General_CI_AS
Rom     varchar(100)        Latin1_General_CI_AS
*/

-现在，我们检查一下 aBc与 ABC的简单比较

--Now we check a simple comparison of "aBc" against "ABC"

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CI  CI*/

-但是我们可以为一个给定的动作指定排序规则！

--But we can specify the collation for one given action!

SELECT CASE WHEN @deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN @Arab = 'ABC' COLLATE Arabic_100_CS_AS_WS_SC THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN @Rom = 'ABC' COLLATE Romanian_CI_AI THEN 'CI' ELSE 'CS' END AS CheckRom

/*CI    CS  CI*/

-但是表的列的行为会有所不同：

--But a table's column will behave differently:

CREATE TABLE #tempTable(deflt VARCHAR(100)
                       ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                       ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO #tempTable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT [name], system_type_name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM #tempTable',NULL,0);
DROP TABLE #tempTable;

/*
name    system_type_name    collation_name
deflt   varchar(100)        Latin1_General_CI_AS
Arab    varchar(100)        Arabic_100_CS_AS_WS_SC
Rom     varchar(100)        Romanian_CI_AI
*/

-这也适用于声明的表变量。比较知道指定的排序规则：

--This applys for declared table variables also. The comparison "knows" the specified collation:

DECLARE @TableVariable TABLE(deflt VARCHAR(100)
                            ,Arab VARCHAR(100) COLLATE Arabic_100_CS_AS_WS_SC
                            ,Rom VARCHAR(100) COLLATE Romanian_CI_AI);

INSERT INTO @TableVariable(deflt,Arab,Rom) VALUES('aBc','aBc','aBc');

SELECT CASE WHEN tv.deflt = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckDefault
      ,CASE WHEN tv.Arab = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckArab
      ,CASE WHEN tv.Rom = 'ABC' THEN 'CI' ELSE 'CS' END AS CheckRom
FROM @TableVariable AS tv

/*CI    CS  CI*/

更新某些文档

在此链接，您可以阅读有关详细信息。排序规则不会更改值。它应用一条规则（与 NOT NULL 相关，该规则不会更改值，而只是添加是否为 NULL 是否可以设置。）

UPDATE Some documentation

At this link You can read about the details. A collation does not change the value. It applys a rule (related to NOT NULL which does not change the values, but just adds the rule whether NULL can be set or not).

文档清楚地说明了

是一个子句，可以应用于数据库定义或列定义以定义归类，或者可以应用于字符串表达式以应用归类强制转换。

Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

稍后，您会发现

1. 创建或更改数据库


2. 创建或更改表列


3. 铸造表达式的排序规则

更新2：解决方案的建议

如果您想控制是否进行比较CS或CI，您可以尝试以下操作：

UPDATE 2: A suggestion for a solution

If you want to have control whether a comparison is done CS or CI you might try this:

DECLARE @tbl TABLE(SomeValueInDefaultCollation VARCHAR(100));
INSERT INTO  @tbl VALUES ('ABC'),('aBc');

DECLARE @CompareCaseSensitive BIT = 0;
DECLARE @SearchFor VARCHAR(100) = 'aBc';

SELECT *
FROM @tbl 
WHERE (@CompareCaseSensitive=1 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CS_AS)
   OR (ISNULL(@CompareCaseSensitive,0)=0 AND SomeValueInDefaultCollation=@SearchFor COLLATE Latin1_General_CI_AS);

将 @CompareCaseSensitive 设置为 1 ，它将仅返回 aBc ，其中 NULL 或 0 会返回两行。

With @CompareCaseSensitive set to 1 it will return just the aBc, with NULL or 0 it will return both lines.

这是-当然！ -性能比UDF好得多。

This is - for sure! - much better in performance than an UDF.

这篇关于UDF中的COLLATE无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！