时间戳

首页 > 解决方案 > 为什么搜索 n 个空格字符的字符串会返回不一致的结果？

sql-server - 为什么搜索 n 个空格字符的字符串会返回不一致的结果？

问题描述

客户发现了一个错误，在尝试修复它时，我遇到了这种奇怪的行为。我已经花了几个小时对此进行了不同的测试，但我仍在努力理解它。在 SQL2016 和 2017（最新 CU）和不同版本的管理工作室上绝对可以重现。下面的示例是取自 1000 行 proc 的简化示例：

要开始创建必要的对象：

CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE test (
id int IDENTITY,
pdate datetime);
INSERT test
VALUES('1-Feb-2018'),('1-Mar-2018'),(NULL);

现在快速查看一下表格中的值：

SELECT * FROM test

现在我想将 pdate 转换为特定格式，如果 pdate 为 NULL，则返回一个空字符串：

SELECT * FROM
    (
        SELECT
            id,
            IIF(pdate IS NULL, '', FORMAT(pdate, 'yyyy-MM-dd')) pdate2
        FROM test
    ) a
WHERE pdate2 = ''

上面的查询产生 id 3 和一个空字符串，这是预期的结果。但是当我在引号之间放置任意数量的空格字符时，我仍然会在结果中得到相同的行：

SELECT * FROM
    (
        SELECT
            id,
            IIF(pdate IS NULL, '', FORMAT(pdate, 'yyyy-MM-dd')) pdate2
        FROM test
    ) a
WHERE pdate2 = '    '

为什么会发生这种情况？

如果我使用不带通配符的 LIKE 而不是等于，即：

SELECT * FROM
    (
        SELECT
            id,
            IIF(pdate IS NULL, '', FORMAT(pdate, 'yyyy-MM-dd')) pdate2
        FROM test
    ) a
WHERE pdate2 LIKE '    '

这给了我预期的结果 - 没有返回任何行。

这是一个错误，还是有一些深层次的技术原因可以将空字符串视为等于 n 个空格字符的字符串？如果是这样，那么为什么 LIKE 会给我正确的结果？

标签： sql-servertsql

解决方案

根据 ANSI SQL 标准，字符字段比较通常会忽略尾随空格。所以这：

SELECT 1 WHERE '' = '     '

将返回1。

KB316626 INF：SQL Server 如何将字符串与尾随空格进行比较 描述了这种行为（强调我的）：

SQL Server 遵循关于如何将字符串与空格进行比较的 ANSI/ISO SQL-92 规范（第 8.2 节，<比较谓词>，一般规则 #3）。ANSI 标准要求对比较中使用的字符串进行填充，以便它们的长度在比较之前匹配。填充直接影响 WHERE 和 HAVING 子句谓词的语义以及其他 Transact-SQL 字符串比较。例如，Transact-SQL 认为字符串“abc”和“abc”对于大多数比较操作是等效的。

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

[...]

SET ANSI_PADDING 设置不影响 SQL Server 在比较它们之前是否填充字符串。SET ANSI_PADDING 仅影响是否从插入到表中的值中修剪尾随空白，因此它影响存储但不影响比较。

请注意，它们还涵盖了您在 LIKE 中发现的差异。

Brent Ozar在此处发布有关此行为的博客。

推荐阅读