SQL Server 2008 空字符串与空格 [英] SQL Server 2008 Empty String vs. Space

问题描述

今天早上我遇到了一些奇怪的事情，我想我会提交评论.

I ran into something a little odd this morning and thought I'd submit it for commentary.

有人可以解释为什么以下 SQL 查询在针对 SQL 2008 运行时打印相等".数据库兼容性级别设置为 100.

Can someone explain why the following SQL query prints 'equal' when run against SQL 2008. The db compatibility level is set to 100.

if '' = ' '
    print 'equal'
else
    print 'not equal'

这将返回 0:

select (LEN(' '))

它似乎是自动修剪空间.我不知道在以前版本的 SQL Server 中是否也是这种情况，我什至没有任何地方可以测试它.

It appears to be auto trimming the space. I have no idea if this was the case in previous versions of SQL Server, and I no longer have any around to even test it.

我遇到了这个问题，因为生产查询返回了不正确的结果.我在任何地方都找不到这种行为的记录.

I ran into this because a production query was returning incorrect results. I cannot find this behavior documented anywhere.

有人知道这方面的信息吗?

Does anyone have any information on this?

推荐答案

varchar 和相等在 TSQL 中是棘手的.LEN 函数说:

varchars and equality are thorny in TSQL. The LEN function says:

返回给定字符串表达式的字符数，而不是字节数，不包括尾随空格.

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

您需要使用 DATALENGTH 来获得相关数据的真实 byte 计数.如果你有unicode数据，注意你在这种情况下得到的值和文本的长度是不一样的.

You need to use DATALENGTH to get a true byte count of the data in question. If you have unicode data, note that the value you get in this situation will not be the same as the length of the text.

print(DATALENGTH(' ')) --1
print(LEN(' '))        --0

当涉及到表达式的相等性时，两个字符串的相等性比较如下:

When it comes to equality of expressions, the two strings are compared for equality like this:

• 获取更短的字符串
• 用空格填充直到长度等于更长的字符串
• 比较两者

• Get Shorter string
• Pad with blanks until length equals that of longer string
• Compare the two

这是导致意外结果的中间步骤 - 在该步骤之后，您有效地将空白与空白进行比较 - 因此它们被视为相等.

It's the middle step that is causing unexpected results - after that step, you are effectively comparing whitespace against whitespace - hence they are seen to be equal.

LIKE 在空白"情况下比 = 表现得更好，因为它不会对您尝试匹配的模式执行空白填充:

LIKE behaves better than = in the "blanks" situation because it doesn't perform blank-padding on the pattern you were trying to match:

if '' = ' '
print 'eq'
else
print 'ne'

将给 eq 同时:

if '' LIKE ' '
print 'eq'
else
print 'ne'

会给 ne

尽管使用 LIKE 需要小心:它不是对称的:它在模式 (RHS) 中将尾随空格视为重要，但在匹配表达式 (LHS) 中不重要.以下内容摘自此处:

Careful with LIKE though: it is not symmetrical: it treats trailing whitespace as significant in the pattern (RHS) but not the match expression (LHS). The following is taken from here:

declare @Space nvarchar(10)
declare @Space2 nvarchar(10)

set @Space = ''
set @Space2 = ' '

if @Space like @Space2
print '@Space Like @Space2'
else
print '@Space Not Like @Space2'

if @Space2 like @Space
print '@Space2 Like @Space'
else
print '@Space2 Not Like @Space'

@Space Not Like @Space2
@Space2 Like @Space

这篇关于SQL Server 2008 空字符串与空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！