SQL Server 2008 Empty String vs. Space

Question

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

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'

And this returns 0:

select (LEN(' '))

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?

Answer 1

There was a similar question a while ago where I looked into a similar problem here

Instead of LEN(' '), use DATALENGTH(' ') - that gives you the correct value.

The solutions were to use a LIKE clause as explained in my answer in there, and/or include a 2nd condition in the WHERE clause to check DATALENGTH too.

Have a read of that question and links in there.

Answer 2

I found this blog article which describes the behavior and explains why.

The SQL standard requires that string comparisons, effectively, pad the shorter string with space characters. This leads to the surprising result that N'' = N' ' (the empty string equals a string of one or more space characters) and more generally any string equals another string if they differ only by trailing spaces. This can be a problem in some contexts.

More information also available in MSKB316626

Answer 3

How to distinct records on select with fields char/varchar on sql server: example:

declare @mayvar as varchar(10)

set @mayvar = 'data '

select mykey, myfield from mytable where myfield = @mayvar

expected

mykey (int) | myfield (varchar10)

1 | 'data '

obtained

mykey | myfield

1 | 'data' 2 | 'data '

even if I write select mykey, myfield from mytable where myfield = 'data' (without final blank) I get the same results.

how I solved? In this mode:

select mykey, myfield
from mytable
where myfield = @mayvar 
and DATALENGTH(isnull(myfield,'')) = DATALENGTH(@mayvar)

and if there is an index on myfield, it'll be used in each case.

I hope it will be helpful.

Answer 4

To compare a value to a literal space, you may also use this technique as an alternative to the LIKE statement:

IF ASCII('') = 32 PRINT 'equal' ELSE PRINT 'not equal'

Answer 5

Sometimes one has to deal with spaces in data, with or without any other characters, even though the idea of using Null is better - but not always usable. I did run into the described situation and solved it this way:

... where ('>' + @space + '<') <> ('>' + @space2 + '<')

Of course you wouldn't do that fpr large amount of data but it works quick and easy for some hundred lines ...

Herbert

Answer 6

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.

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 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'

Will give eq while:

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

Will give ne

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