Posted by othellomy on 03/21/07 07:32

On Mar 21, 12:12 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 20 Mar 2007 03:04:36 -0700, othell...@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

SET @a = ''
SET @b = ' '
if nullif(@a,'') is null and nullif(@b,'') is null
PRINT 'They are equal!';
ELSE
PRINT 'They are different!';

[Back to original message]