sql-server - 为什么 EXCEPT 子句会在文本末尾修剪空白？

Question

我通读了 SqlServer EXCEPT 运算符的文档，但没有看到在字符串末尾显式修剪空白。但是，运行时：

SELECT 'Test'
EXCEPT
SELECT 'Test '

不返回任何结果。任何人都可以解释这种行为或在使用 EXCEPT 时如何避免它？

标签： sql-server

Answer 1

ANSI SQL-92 要求字符串在比较之前长度相同，并且填充字符是空格。

有关详细信息，请参阅https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces

在 ANSI 标准中（在此处访问第 8.2 节）

3) 两个字符串的比较确定如下：

        a) If the length in characters of X is not equal to the length
          in characters of Y, then the shorter string is effectively
          replaced, for the purposes of comparison, with a copy of
          itself that has been extended to the length of the longer
          string by concatenation on the right of one or more pad char-
          acters, where the pad character is chosen based on CS. If
          CS has the NO PAD attribute, then the pad character is an
          implementation-dependent character different from any char-
          acter in the character set of X and Y that collates less
          than any string under CS. Otherwise, the pad character is a
          <space>.

        b) The result of the comparison of X and Y is given by the col-
          lating sequence CS.

        c) Depending on the collating sequence, two strings may com-
          pare as equal even if they are of different lengths or con-
          tain different sequences of characters. When the operations
          MAX, MIN, DISTINCT, references to a grouping column, and the
          UNION, EXCEPT, and INTERSECT operators refer to character
          strings, the specific value selected by these operations from
          a set of such equal values is implementation-dependent.

如果必须避免这种行为，您可以反转列作为您的一部分，除了：

SELECT 'TEST', REVERSE('TEST')
EXCEPT
SELECT 'TEST ', REVERSE('TEST ')

这给出了预期的结果，但很烦人，尤其是在处理多列时。

另一种方法是找到一个带有备用填充字符或无填充选项集的整理序列，尽管在快速谷歌之后这似乎在 t-sql 中不存在。

或者，您可以用一个字符终止每一列，然后在最后将其子串出来：

SELECT SUBSTRING(col,1,LEN(col) -1) FROM 
(
    SELECT 'TEST' + '^' as col
    EXCEPT 
    SELECT 'TEST ' + '^'
) results