SQL WHERE 子句匹配带有尾随空格的值 [英] SQL WHERE clause matching values with trailing spaces

问题描述

在 SQL Server 2008 中，我有一个名为 Zone 的表，其中有一列 ZoneReference varchar(50) not null 作为主键.

In SQL Server 2008 I have a table called Zone with a column ZoneReference varchar(50) not null as the primary key.

如果我运行以下查询:

select '"' + ZoneReference + '"' as QuotedZoneReference
from Zone
where ZoneReference = 'WF11XU'

我得到以下结果:

"WF11XU "

注意尾随空格.

这怎么可能?如果该行上确实存在尾随空格，那么我希望返回 零 结果，因此我假设 SQL Server Management Studio 显示的其他内容很奇怪.

How is this possible? If the trailing space really is there on that row, then I'd expect to return zero results, so I'm assuming it's something else that SQL Server Management Studio is displaying weirdly.

在 C# 代码中调用 zoneReference.Trim() 将其删除，表明它是某种空白字符.

In C# code calling zoneReference.Trim() removes it, suggesting it is some sort of whitespace character.

有人可以帮忙吗?

推荐答案

这是预期的结果:在 SQL Server 中，= 运算符在进行比较时会忽略尾随空格.

That's the expected result: in SQL Server the = operator ignores trailing spaces when making the comparison.

SQL Server 遵循 ANSI/ISO SQL-92 规范(第 8.2 节，一般规则 #3)关于如何将字符串与空格进行比较.ANSI 标准要求对比较中使用的字符串进行填充，以便在比较它们之前它们的长度匹配.填充直接影响 WHERE 和 HAVING 子句谓词以及其他 Transact-SQL 字符串比较的语义.例如，Transact-SQL 认为字符串 'abc' 和 'abc ' 对于大多数比较操作是等效的.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

此规则的唯一例外是 LIKE 谓词.当 LIKE 谓词表达式的右侧包含一个带有尾随空格的值时，SQL Server 在比较之前不会将这两个值填充到相同的长度.因为根据定义，LIKE 谓词的目的是促进模式搜索而不是简单的字符串相等性测试，所以这不违反前面提到的 ANSI SQL-92 规范部分.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

来源

这篇关于SQL WHERE 子句匹配带有尾随空格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！