|
|
Posted by David Portas on 03/20/07 09:52
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
--
Navigation:
[Reply to this message]
|