主页 / dba / 问题 / 56876
Accepted
WT_W
Asked: 2014-01-16 23:25:41 +0800 CST

SQL Server在相等比较中自动修剪varchar值但不是比较

  • 772

今天我在 SQL Server 上遇到了一些有趣的行为（在 2005 年和 2012 年观察到），我希望有人能解释一下。

=使用NVARCHAR 字段进行比较的查询忽略了字符串中的尾随空格（或在比较之前自动修剪值），但使用like运算符的相同查询没有忽略空格。使用的排序规则是 2012 年的 Latin1_General_CI_AS。

考虑这个 SQL Fiddle：http ://sqlfiddle.com/#!6/72262/4

请注意，like运算符不会返回尾随空格字符串的结果，但=运算符会。为什么是这样？

加分项：我无法在 VARCHAR 字段上复制它，我原以为在两种数据类型中都会以相同的方式处理空格 - 这是真的吗？

sql-server sql-server-2012

3 个回答

  1. Best Answer

    我最初的回答表明，设置为 OFF 的 ANSI_PADDING 标志可能是造成行为差异的原因。但是，这是不正确的；此标志仅对存储有影响，但对相等比较没有影响。

    差异源于微软对 SQL 标准的实现。该标准规定，在检查相等性时，相等运算符的左右字符串都必须填充为具有相同的长度。这解释了以下结果：

    insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1

    LIKE 运算符不填充其操作数。对于VARCHAR和NVARCHAR列类型，它的行为也不同：

    -- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0

    ASCII 类型的 LIKE 运算符的行为是特定于 SQL Server 的；对于 Unicode 类型，它符合 ANSI。

    • 18

  2. SQL 诞生于大多数数据处理语言对每个字段/变量使用固定长度的时代。带有额外空格的文本字段的自动填充也是该图片的一部分。为了与这种行为保持一致，原始 SQL CHAR 类型被明确定义为其 '=' 运算符以忽略尾随空格。（如果你觉得这很奇怪，请给我看一个令人信服的案例，其中附加到文本的尾随空格具有实际的真正商业意义。）

    从那时起，SQL CHAR 类型已经向各个方向发展，但某些更现代的数据类型仍然继承了其历史前辈的一些特征并非不可想象。

    • 4

  3. 在LIKE (Transact-SQL)的文档中，微软写道（强调我的）：

    使用 LIKE 进行模式匹配

    LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配。当所有参数 ... 都是 ASCII 字符数据类型时，执行 ASCII 模式匹配。如果任何一个参数是 Unicode 数据类型，则所有参数都将转换为 Unicode 并执行 Unicode 模式匹配。当您使用 Unicode 数据 ... 与 LIKE 时，尾随空格很重要；但是，对于非 Unicode 数据，尾随空格并不重要。Unicode LIKE 与 ISO 标准兼容。ASCII LIKE 与早期版本的 SQL Server 兼容。

    • 1

相关问题