create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)

select *
from t1

c1 c2
1 Hello
2
3 NULL

select *
from t1
where c2 = ' '

c1 c2
2


select *
from t1
where ltrim(rtrim(c2)) is null

c1 c2
3 NULL

The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1 c2
2
3 NULL

On 20 Mar, 06:28, othell...@yahoo.com wrote:
> create table t1(c1 int, c2 varchar(10))
> insert t1 values(1,'Hello')
> insert t1 values(2,'')
> insert t1 values(3,NULL)
>
> select *
> from t1
>
> c1 c2
> 1 Hello
> 2
> 3 NULL
>
> select *
> from t1
> where c2 = ' '
>
> c1 c2
> 2
>
> select *
> from t1
> where ltrim(rtrim(c2)) is null
>
> c1 c2
> 3 NULL
>
> The last query should have result as following. However sql server
> 2000 does no list row c1 = 2.
> c1 c2
> 2
> 3 NULL

Why would you think that the result of ltrim(rtrim(c2)) would be NULL
when c2 is a non-null string? In fact the result is an empty string
(not the same as NULL) so the answer you got is correct. The row where
c1=2 should NOT be included.

In SQL, NULL is not the same as an empty string. The only common
exception that I know of is Oracle, which treats empty strings as
NULLs.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

On Mar 20, 3:52 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 20 Mar, 06:28, othell...@yahoo.com wrote:
>
>
>
>
>
> > create table t1(c1 int, c2 varchar(10))
> > insert t1 values(1,'Hello')
> > insert t1 values(2,'')
> > insert t1 values(3,NULL)
>
> > select *
> > from t1
>
> > c1 c2
> > 1 Hello
> > 2
> > 3 NULL
>
> > select *
> > from t1
> > where c2 = ' '
>
> > c1 c2
> > 2
>
> > select *
> > from t1
> > where ltrim(rtrim(c2)) is null
>
> > c1 c2
> > 3 NULL
>
> > The last query should have result as following. However sql server
> > 2000 does no list row c1 = 2.
> > c1 c2
> > 2
> > 3 NULL
>
> Why would you think that the result of ltrim(rtrim(c2)) would be NULL
> when c2 is a non-null string? In fact the result is an empty string
> (not the same as NULL) so the answer you got is correct. The row where
> c1=2 should NOT be included.
>
> In SQL, NULL is not the same as an empty string. The only common
> exception that I know of is Oracle, which treats empty strings as
> NULLs.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
> --- Hide quoted text -
>
> - Show quoted text -

If it is not null then it is definitely not 'any number of spaces' and
match.

select *
from t1
where c2 = ' '

Actually, ltrim(rtrim(c2)) is 'any number of spaces', it's zero
spaces, or empty string, not NULL. NULL is not an empty string, it is
NULL. End of story.

Cheers,
Jason Lepack

On Mar 20, 6:04 am, othell...@yahoo.com wrote:
> On Mar 20, 3:52 pm, "David Portas"
>
>
>
> <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> > On 20 Mar, 06:28, othell...@yahoo.com wrote:
>
> > > create table t1(c1 int, c2 varchar(10))
> > > insert t1 values(1,'Hello')
> > > insert t1 values(2,'')
> > > insert t1 values(3,NULL)
>
> > > select *
> > > from t1
>
> > > c1 c2
> > > 1 Hello
> > > 2
> > > 3 NULL
>
> > > select *
> > > from t1
> > > where c2 = ' '
>
> > > c1 c2
> > > 2
>
> > > select *
> > > from t1
> > > where ltrim(rtrim(c2)) is null
>
> > > c1 c2
> > > 3 NULL
>
> > > The last query should have result as following. However sql server
> > > 2000 does no list row c1 = 2.
> > > c1 c2
> > > 2
> > > 3 NULL
>
> > Why would you think that the result of ltrim(rtrim(c2)) would be NULL
> > when c2 is a non-null string? In fact the result is an empty string
> > (not the same as NULL) so the answer you got is correct. The row where
> > c1=2 should NOT be included.
>
> > In SQL, NULL is not the same as an empty string. The only common
> > exception that I know of is Oracle, which treats empty strings as
> > NULLs.
>
> > --
> > David Portas, SQL Server MVP
>
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
>
> > SQL Server Books Online:http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
> > --- Hide quoted text -
>
> > - Show quoted text -
>
> If it is not null then it is definitely not 'any number of spaces' and
> match.
>
> select *
> from t1
> where c2 = ' '

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

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

On 21 Mar 2007 00:32:20 -0700, othellomy@yahoo.com wrote:

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

Hi othellomy,

I'm not sure what you're trying to say here. This code will return "They
are equal!" if both @a and @b are either NULL or a string consisting of
zero or more space characters, regardless of whether they are equal:

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

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

But it will return nothing if @a and @b are both non-NULL and not empty,
even if they ARE equal:

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

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

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