为什么相等运算符和类似运算符之间的相等比较之间存在差异? [英] Why is there is a difference between equality comparison between equal operator and like operator?

问题描述

SELECT au_lname, au_fname来自作者WHERE au_lname = '绿色'

<前>au_lname au_fname----------------------------------------- --------------------绿色玛乔丽

SELECT au_lname, au_fname来自作者WHERE au_lname LIKE '绿色'

<前>au_lname au_fname----------------------------------------- --------------------

谁能解释一下为什么第二个查询没有返回预期的行?

解决方案

这显然是标准的一个怪癖.也可能是明确的:

<块引用>

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

此规则的唯一例外是 LIKE 谓词.当右侧LIKE 谓词表达式具有带有尾随空格的值，SQL服务器不会将这两个值填充到比较前长度相同发生.因为本次活动的目的LIKE 谓词，根据定义，是促进模式搜索而不是比简单的字符串相等测试，这不违反第ANSI SQL-92 规范前面提到过.

请参阅此处，这里 和 此处.

奇怪 - 考虑到平等"和喜欢"这两个词，我原以为后者更自由.

SELECT au_lname, au_fname  
FROM authors
WHERE au_lname = 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------
Green                                    Marjorie

SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE 'Green '

au_lname                                 au_fname
---------------------------------------- --------------------

Could anyone please explain me why is the second query is not returning the expected row?

解决方案

It's a quirk of the standards apparently. Might as well be explicit:

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.

See here, here and here.

Bizarre - given the terms "equal" and "like" I would have expected the latter to be the more liberal.

这篇关于为什么相等运算符和类似运算符之间的相等比较之间存在差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！