SQL字符串与EXCEPT的相等性比较

I've just realised that there is a problem with a couple of triggers I have written which can be demonstrated with this query here:

我刚刚意识到我写的几个触发器存在问题,可以在此处使用此查询进行演示:

SELECT CASE WHEN 'TEST' = 'TEST ' THEN 'y' ELSE 'n' END

当'TEST'='TEST'然后''''''''时选择案例

Whilst you might think that this resolves to N, it in fact resolves to Y as SQL Server trims the trailing spaces from strings before comparing them in this way.

虽然您可能认为这解析为N,但它实际上解析为Y,因为SQL Server在以这种方式比较它们之前修剪字符串的尾随空格。

One suggestion appears to be to use like, which is fine in a where clause:

一个建议似乎是使用like,这在where子句中很好:

SELECT CASE WHEN 'TEST' LIKE 'TEST ' THEN 'y' ELSE 'n' END does indeed resolve to n as you would hope.

选择案例'测试'喜欢'测试'然后''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Another suggestion is to check the length too, using `DATALENGTH:

另一个建议是使用`DATALENGTH检查长度:

SELECT CASE WHEN 'TEST' = 'TEST ' AND DATALENGTH('TEST') = DATALENGTH('TEST ') THEN 'y' ELSE 'n' END

选择案例'TEST'='TEST'和DATALENGTH('TEST')= DATALENGTH('TEST')那么''''''''结束

Unfortuantely, this is going to cause extra complexity in a trigger I use to update another table:

不幸的是,这会在我用来更新另一个表的触发器中造成额外的复杂性:

SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB

Which then becomes:

然后变成:

SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS,
    FullName_Length = DATALENGTH(FullName)
FROM TableA
EXCEPT
SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS,
    FullName_Length = DATALENGTH(FullName)
FROM TableB

Is there a simpler way to do this? Is there some way I can tell EXCEPT to compare strings including their length rather than by trimming the trailing characters?

有更简单的方法吗?有什么方法可以告诉我除了比较字符串,包括它们的长度,而不是通过修剪尾随字符?

Apologies if this has been asked before, I haven't been very lucky with finding anything specifically looking at EXCEPT.

如果以前曾经问过这个问题,我很遗憾找到任何特别注意的东西都不是很幸运。

1 个解决方案

#1

Well, you could add something at the end

好吧,你可以在最后添加一些东西

SELECT 
      CASE WHEN CONCAT('TEST', 'ADummyValue') = 
             CONCAT('TEST      ', 'ADummyValue') THEN 'y' ELSE 'n' END


SELECT
    CONCAT(FullName, 'ADummyValue') AS FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    CONCAT(FullName, 'ADummyValue') COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB

Using REVERSE

SELECT
    REVERSE(FullName) AS FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    REVERSE(FullName) COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB