条件不考虑空格的SQL Server

I have first name and last name with trailing white spaces. I have two SQL queries – the first query is returning result even if I search with no white space. (The first query is returning unwanted result).

我有名字和姓氏，后面有空格。我有两个SQL查询——第一个查询返回结果，即使我搜索时没有空格。(第一个查询返回不想要的结果)。

Is this behavior consistent in all versions of SQL Server?
这种行为在所有版本的SQL Server中都是一致的吗?
Is it a known behavior? Is it documented anywhere in msdn for SQL Server 2008 R2?
这是已知的行为吗?SQL Server 2008 R2的msdn上是否有文档记录?

CODE

代码

DECLARE @NameTable TABLE (first_name varchar(40),last_name varchar(40))
INSERT INTO @NameTable VALUES ('STEVEN    ','STANLEY   ');


--QUERY 1
SELECT first_name AS [FirstName], last_name AS [LastName]
FROM  @NameTable A
WHERE (first_name = 'STEVEN')
AND (last_name = 'STANLEY')


--QUERY 2       
SELECT first_name AS [FirstName], last_name AS [LastName]
FROM  @NameTable A
WHERE (( ISNULL(first_name,'')+' ' +ISNULL(last_name,'') ) = 'STEVEN STANLEY')

--QUERY 3   (With LTRIM and RTRIM)    
SELECT first_name AS [FirstName], last_name AS [LastName]
FROM  @NameTable A
WHERE (( ISNULL(LTRIM(RTRIM(first_name)),'')+' ' +ISNULL(LTRIM(RTRIM(last_name)),'') ) = 'STEVEN STANLEY')

Reference:

参考:

DataLength
DataLength
In SQL Server 2005, what is the difference between len() and datalength()?
在SQL Server 2005中，len()和datalength()之间的区别是什么?

2 个解决方案

#1

Refer http://support.microsoft.com/kb/316626

请参阅http://support.microsoft.com/kb/316626

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.

SQL Server遵循ANSI/ISO SQL-92规范(第8.2节，通用规则#3)，说明如何将字符串与空格进行比较。ANSI标准要求对用于比较的字符串进行填充，以便它们的长度在比较之前匹配。填充直接影响WHERE和have子句谓词以及其他Transact-SQL字符串比较的语义。例如，Transact-SQL将字符串“abc”和“abc”视为与大多数比较操作相同的字符串。

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.

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

If you want to avoid this you can add an additions condition, would look like the following

如果您想避免这种情况，可以添加一个附加条件，如下所示

Refer: DataLength

参考:DataLength

SELECT first_name AS [FirstName], last_name AS [LastName],first_name+last_name,LEN(first_name+last_name)
FROM  @NameTable A
WHERE (first_name = 'STEVEN') and DATALENGTH(first_name)=DATALENGTH(RTRIM(first_name))
AND (last_name = 'STANLEY') and DATALENGTH(last_name)=DATALENGTH(RTRIM(last_name))

#2

I can't say however i've something related with comparison of strings with trailing white space --- http://support.microsoft.com/kb/316626

但我不能说，我有一些与字符串后面的空格比较的东西——http://support.microsoft.com/kb/316626。

(EDIT)

(编辑)

Can you check this code?

你能检查一下这段代码吗?

SELECT first_name AS [FirstName], last_name AS [LastName]
FROM  @NameTable A
WHERE (left(first_name,6) = 'STEVEN')+' '+(right(last_name,6) = 'STANLEY')

(for names with 6 characters)

(六字名称)

#1