Posted by Jason Lepack on 03/20/07 10:53

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/ms130214(en-US,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 = ' '

[Back to original message]