insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)
select *
from t1
c1c2
1Hello
2
3NULL
select *
from t1
where c2 = ' '
c1c2
2
select *
from t1
where ltrim(rtrim(c2)) is null
c1c2
3NULL
The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1c2
2
3NULLOn 20 Mar, 06:28, othell...@.yahoo.com wrote:
Quote:
Originally Posted by
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/...US,SQL.90).aspx
--|||On Mar 20, 3:52 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:
Quote:
Originally Posted by
On 20 Mar, 06:28, othell...@.yahoo.com wrote:
>
>
>
>
>
Quote:
Originally Posted by
create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)
>
Quote:
Originally Posted by
select *
from t1
>
Quote:
Originally Posted by
c1 c2
1 Hello
2
3 NULL
>
Quote:
Originally Posted by
select *
from t1
where c2 = ' '
>
Quote:
Originally Posted by
c1 c2
2
>
Quote:
Originally Posted by
select *
from t1
where ltrim(rtrim(c2)) is null
>
Quote:
Originally Posted by
c1 c2
3 NULL
>
Quote:
Originally Posted by
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/...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 = ' '|||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:
Quote:
Originally Posted by
On Mar 20, 3:52 pm, "David Portas"
>
>
>
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:
Quote:
Originally Posted by
On 20 Mar, 06:28, othell...@.yahoo.com wrote:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
create table t1(c1 int, c2 varchar(10))
insert t1 values(1,'Hello')
insert t1 values(2,'')
insert t1 values(3,NULL)
>
Quote:
Originally Posted by
Quote:
Originally Posted by
select *
from t1
>
Quote:
Originally Posted by
Quote:
Originally Posted by
c1 c2
1 Hello
2
3 NULL
>
Quote:
Originally Posted by
Quote:
Originally Posted by
select *
from t1
where c2 = ' '
>
Quote:
Originally Posted by
Quote:
Originally Posted by
c1 c2
2
>
Quote:
Originally Posted by
Quote:
Originally Posted by
select *
from t1
where ltrim(rtrim(c2)) is null
>
Quote:
Originally Posted by
Quote:
Originally Posted by
c1 c2
3 NULL
>
Quote:
Originally Posted by
Quote:
Originally Posted by
The last query should have result as following. However sql server
2000 does no list row c1 = 2.
c1 c2
2
3 NULL
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
--
David Portas, SQL Server MVP
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
SQL Server Books Online:http://msdn2.microsoft.com/library/...US,SQL.90).aspx
-- Hide quoted text -
>
Quote:
Originally Posted by
- 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)
Quote:
Originally Posted by
>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.INVALIDwrote:
Quote:
Originally Posted by
On 20 Mar 2007 03:04:36 -0700, othell...@.yahoo.com wrote:
>
(snip)
>
Quote:
Originally Posted by
If it is not null then it is definitely not 'any number of spaces' and
match.
>
Quote:
Originally Posted by
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)
Quote:
Originally Posted by
>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
No comments:
Post a Comment