Posted by Hugo Kornelis on 03/20/07 18:12

On 20 Mar 2007 03:04:36 -0700, othellomy@yahoo.com wrote:

(snip)
>If it is not null then it is definitely not 'any number of spaces' and
>match.
>
>select *
>from t1
>where c2 = ' '

Hi othellomy,

I'm not sure if I understand you correctly, but I assume that you are
asking why a string of zero length ('') is considered equal to a string
of spaces (' ').

The reason is how ANSI has ruled that string comparisons in SQL should
be carried out: the shorter string has to be padded with spaces to match
the length of the longer string; after that, the strings are compared
position by position.

I know that this is not always the behaviour people expect and require.
The expectation can be managed by understanding the rules for string
comparisons. And the required behaviour of string comparisons can be
gotten by using one of the followinmg two workarounds:

DECLARE @a varchar(10), @b varchar(10);
SET @a = 'abc';
SET @b = 'abc ';

-- Workaround 1
IF @a = @b AND DATALENGTH(@a) = DATALENGTH(@b)
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';

-- Workaround 2
IF @a + 'X' = @b + 'X'
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]