使用 IF 进行 SQL 字符串比较

Question

这可能是一个愚蠢的问题，但是谁能解释一下为什么 SQL 返回 'False'

IF 'test' = ' test' -- notice leading space
 SELECT 'True'
ELSE
 SELECT 'False'

但返回'True'

IF 'test' = 'test ' -- notice trailing space
 SELECT 'True'
ELSE
 SELECT 'False'

编辑:

我使用的是 SQL Server 2008 R2

Answer 1

忽略尾随空格。

如果你想真正测试它们是否相同，那么像这样:

DECLARE @foo nvarchar(50) = 'foo'
DECLARE @foo2 nvarchar(50) = 'foo ' -- trailing space

IF @foo = @foo2 AND DATALENGTH(@foo) = DATALENGTH(@foo2) --LEN ignores trailing spaces
    SELECT 'true'
ELSE
    SELECT 'false'

为什么你的例子是正确的:

http://www.timvw.be/2013/04/27/the-curious-case-of-trailing-spaces-in-sql/

根据 http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt :

 3) The comparison of two character strings is determined as fol-
        lows:

        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
          .

        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.