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

问题描述

在SQL Server 2008中我有一个名为区表列 ZoneReference VARCHAR（50）NOT NULL 作为主键

如果我运行下面的查询：

 选择'+ ZoneReference +''作为QuotedZoneReference
从区
其中，ZoneReference ='WF11XU
 

我得到以下结果：

 WF11XU
 

请注意尾随空格。

这怎么可能？如果尾随的空间真的是有在该行，那么我会希望返回的零的结果，所以我猜想这是别的，SQL Server Management Studio中的古怪显示。

在C＃code调用 zoneReference.Trim（）中删除，这意味着它是某种空白字符的。

谁能帮助？

解决方案

这是预期的结果：在SQL Server中的 = 运营商忽略在进行比较时尾随空格<。 / p>

  

SQL Server遵循了ANSI / ISO SQL-92规范对如何使用字符串比较的空间（第8.2节，一般规则＃3）。 ANSI标准要求在比较中使用的字符串填充，使它们的长度比较之前匹配。填充直接影响WHERE和HAVING子句predicates和其他的Transact-SQL字符串比较的语义。例如在Transact-SQL认为字符串ABC和ABC是等价的大多数比较操作。

  
  

唯一的例外是LIKE predicate。当LIKE predicate前pression的右侧设有一个尾随空格的值时，SQL Server比较发生之前没有垫两个值的长度相同。因为LIKE predicate的目的，顾名思义，是为了方便模式搜索，而不是简单的字符串相等测试，这并不违反前面提到的ANSI SQL-92规范的部分。

来源

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

If I run the following query:

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

I get the following result:

"WF11XU "

Note the trailing space.

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.

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

Can anyone help?

解决方案

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

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.

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.

Source

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