SQL Server 2008 Empty String vs. Space

user_1511 0
Viewed 37703

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?

datalength string-length sql-server-2008 tsql sql-server
9 Answers

Another way is to put it back into a state that the space has value. eg: replace the space with a character known like the _

if REPLACE('hello',' ','_') = REPLACE('hello ',' ','_')
    print 'equal'
else
    print 'not equal'

returns: not equal

Not ideal, and probably slow, but is another quick way forward when needed quickly.

user_3238060

As SQL - 92 8.2 comparison predicate saying:

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

user_82579730
Related