SQL Server nvarchar与空白空间的比较

We've got interesting issue from production today :) Everything is fine now but I still don't understand one thing. Let me show you the query.

我们今天从生产中得到了一个有趣的问题:)现在一切都很好但我仍然不明白一件事。让我告诉你查询。

IF OBJECT_ID('tempdb.dbo.#results', 'U') IS NOT NULL
    DROP TABLE #results;

CREATE TABLE #results(
    [id] smallint,
    [name] nvarchar(128)
)
insert into #results values (1, 'JOHN NOWAK      '), (2, 'frog'), (3, 'wine')

declare @nazwa_p nvarchar(128) = 'JOHN NOWAK';

SELECT * FROM #results WHERE [name] = @nazwa_p
SELECT * FROM #results WHERE [name] like @nazwa_p

First query result is

第一个查询结果是

1   JOHN NOWAK

The second query gives me nothing. Why is that? Does the = operator run RTRIM() method?

第二个查询什么也没给我。这是为什么？ =运算符是否运行RTRIM（）方法？

Thank's for any answers.

谢谢你的任何答案。

2 个解决方案

#1

Trailing spaces are ignored in equality comparisons. In your like clause, you are missing %. I added a new variable to show how this can be done.

在相等比较中忽略尾随空格。在你喜欢的条款中，你缺少％。我添加了一个新变量来说明如何做到这一点。

IF OBJECT_ID('tempdb.dbo.#results', 'U') IS NOT NULL
    DROP TABLE #results;

CREATE TABLE #results(
    [id] smallint,
    [name] nvarchar(128)
)
insert into #results values (1, 'JOHN NOWAK      '), (2, 'frog'), (3, 'wine')

declare @nazwa_p nvarchar(128) = 'JOHN NOWAK';

declare @nazwa_p2 nvarchar(128) = '%JOHN NOWAK%';

SELECT * FROM #results WHERE [name] = @nazwa_p
SELECT * FROM #results WHERE [name] like @nazwa_p2
SELECT * FROM #results WHERE [name] like '%' +  @nazwa_p + '%'

#2

When you use the equal (=) operator, SQL server pads the two values to be of equal length. This is also happening with other operators such as HAVING or WHERE. See the ANSI/ISO SQL-92 specification.

当您使用equal（=）运算符时，SQL Server会将这两个值填充为相等的长度。这也发生在其他运营商，如HAVING或WHERE。请参阅ANSI / ISO SQL-92规范。

The like operator does not do that padding. This is the only difference between using equal operator and the LIKE operator without a wildcard. To obtain the same result you would need:

like运算符不执行该填充。这是使用等于运算符和没有通配符的LIKE运算符之间的唯一区别。要获得相同的结果，您需要：

     SELECT * FROM #results WHERE [name] like CONCAT('[ ]',@nazwa_p,'[ ]')

In this case, LIKE would match any number of spaces before or after the given text. (note that in the example above there is a space in between the square brackets.

在这种情况下，LIKE将匹配给定文本之前或之后的任意数量的空格。（请注意，在上面的示例中，方括号之间有一个空格。

#1