Posted by othellomy on 03/20/07 10:04

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]