SQL Server 是否会在查询时自动修剪 nvarchar 字段? [英] Does SQL Server automatically trim nvarchar fields upon query?
问题描述
我有这个问题:
select '[' + p.Firstname + ']' from Person p
where p.Firstname = 'Johanne'
在表格中，我有多个人都有这个名字，有些人的值有一个尾随空格(值的错误插入，将被纠正).
In the table, I have multiple personne who have this firstname, and some have a trailing space on the value (bad insertion of the values, it will be corrected).
为什么这个查询会给我带来这个结果(我插入了括号来可视化空格):
Why then does this query bring me this result (I inserted the brackets to visualize the spaces) :
[Johanne]
[Johanne ]
[Johanne ]
[Johanne]
这是配置的事情吗?真正的查询来自实体框架 6，但此示例也执行此操作.我该如何预防?
Is this a configuration thing ? The real query comes from entity framework 6, but this example does it also. How can I prevent it ?
谢谢！
我可以使用 EF6 和 System.Data.Entity.SqlServer.SqlFunctions.DataLength 方法使其工作，如下所示:
I could make it work using EF6 and the System.Data.Entity.SqlServer.SqlFunctions.DataLength method like this:
ctx.Person.FirstOrDefault(p => p.FirstName == "Johanne" && SqlFunctions.DataLength(p.FirstName) == "Johanne".Length);
推荐答案
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 Server 是否会在查询时自动修剪 nvarchar 字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
