MS Access/SQL Server Len() 脑筋急转弯

Question

最近我在一个与SQL Server后台相关的表格中寻找错误数据。我运行了以下查询：

SELECT Len(MyField) AS FieldLength, "#" & MyField & "#" AS FieldContents
FROM MyTable
WHERE Len(MyField) <> 10

我得到了以下结果：

FieldLength    FieldContents
-----------------------------
10             #M1023-324 #
10             #X3253-215 #

在明确告诉查询不要返回长度为10的字段后，它仍然返回了几个长度为10的字段记录。所有这些字段都有尾随空格。

我弄清楚了发生了什么事情，这种情况很容易让毫无防备的开发人员/数据库管理员（比如我自己）犯错。所以谁知道这里到底发生了什么？

- mwolfe02

---

根据您下面的提示，我怀疑这可能与ODBC设置或问题有关。 - HK1

Answer 1

@Andriy M赢得了奖品。正如他所猜测的那样，SQL Server处理WHERE子句，而MS Access处理SELECT子句。

通常情况下，这并不引起注意，但是Transact-SQL的LEN()函数在计算字符数之前修剪尾随的空格，而VBA的Len()函数则不会。请阅读下面的完整解释。

我运行了SQL Server Profiler来查看MS Access在幕后实际发送了什么，以下是我得到的结果：

SELECT "dbo"."MyTable"."MyTableID" 
FROM "dbo"."MyTable" 
WHERE NOT(({fn length("MyField" )}= 10 ) )

上面的查询返回了所有符合Len(MyField) <> 10条件的记录的主键。 {fn length()}是一个ODBC规范函数。

MS SQL Server使用自己的Len()函数来实现{fn length()}规范函数。正如@Sir Crispalot在他的答案中指出的那样，Transact-SQL Len函数会修剪掉尾随的空格。因此，在MS SQL Server中，Len('M1023-324 ') = 9。当然，在VBA中，Len("M1023-324 ") = 10。

如果您仔细查看上面的查询，您会发现它实际上并没有返回我在原始SELECT语句中请求的字段。相反，它只返回足够的信息（即主键字段），以便MS Access在需要时请求完整的记录。

这样做是出于性能原因。如果我的查询返回了30,000行，并且每行有三十多个字段，那么如果我一次只显示10行数据，从SQL Server将所有这些信息传输过来就没有意义。所以Access获取所有这些主键，然后一次请求几个来自SQL Server的单独记录。此外，它实际上在SQL Server中创建临时存储过程来执行返回这些单独行的操作。我现在偏离了话题，但是如果您有机会使用SQL Server Profiler，值得进行一些实验，以查看Access在幕后实际执行的操作。

无论如何，在SELECT子句中，MS Access仅从SQL Server请求字段本身的内容。也就是说，它请求MyField而不是'#' & MyField & '#'。这也意味着它请求MyField而不是Len(MyField)。

最终结果是，SQL Server对过滤操作（WHERE子句）进行计算，而MS Access对显示操作（SELECT子句）进行计算。由于Len()函数在每个环境中的行为略有不同，因此我们最终得到了一些人们认为非常令人困惑的结果：

10 <> 10

但这对我来说是很有道理的。

- mwolfe02

Answer 2

我重新制造了where子句的奇怪行为，然后查看了文档。

从MSDN获取的数据 - “返回指定字符串表达式的字符数，不包括尾随空格。”

因此，根据SQL，“M1023-324 ”的长度为9。

仍然无法解释为什么它们在第一列都有10。我无法再现那部分！

- Sir Crispalot

---

我可能在我的问题中没有完全表达清楚，但我是从MS Access中运行此查询的。MyTable表链接到一个位于SQL Server后端的表。 - mwolfe02

那么你正在发布一个你已经知道答案的问题？！这对我来说是新鲜事！ - Sir Crispalot

这实际上是被积极鼓励的：问自己问题并回答自己的问题是可以的。 - mwolfe02

我并没有批评，我也能看出其中的优点！但是我不知道完整的答案 :( - Sir Crispalot

@mwolfe02：是否可能WHERE和SELECT在不同的方面被评估（更具体地说，WHERE显然由SQL Server处理，而SELECT由Access处理）？ - Andriy M

Answer 3

在看到您的提示后：这不是因为Sql Server中的字段是char而不是varchar吗？

- iDevlop

---

迂回地说，这确实导致了问题。该字段实际上是一个varchar字段，但在其生命周期的早期它是一个char字段。每个记录应该有10个字符，因此它被设置为char（10）字段。一些不良数据偶然地滑入其中，长度少于十个字符，从而引入了尾随空格。后来，当需求发生变化时，该字段被转换为varchar（15）字段。但那些带有尾随空格的坏数据仍然存在。 - mwolfe02