473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

nulls

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

Mar 20 '07 #1
6 1834
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
--
Mar 20 '07 #2
On Mar 20, 3:52 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.orgwrote:
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 = ' '

Mar 20 '07 #3
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.orgwrote:
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 = ' '

Mar 20 '07 #4
On 20 Mar 2007 03:04:36 -0700, ot*******@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
Mar 20 '07 #5
On Mar 21, 12:12 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
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!';

Mar 21 '07 #6
On 21 Mar 2007 00:32:20 -0700, ot*******@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
Mar 21 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Dan Perlman | last post by:
From: "Dan Perlman" <dan@dpci.NOSPAM.us> Subject: ODBC creating nulls? Date: Friday, July 09, 2004 10:43 AM Hi, Below is my VB6 code that writes data from an Access 2000 table to a PG table....
2
by: Steve Walker | last post by:
Hi all. I've been tasked with "speeding up" a mid-sized production system. It is riddled with nulls... "IsNull" all over the procs, etc. Is it worth it to get rid of the nulls and not allow...
3
by: aaj | last post by:
Hi I am probably going to regret asking this because I'm sure you are going to tell me my design is bad 8-) ah well we all have to learn.... anyway I often use Nulls as a marker to see if...
6
by: mike | last post by:
I'm doing what I thought was a simple GROUP BY summary of fairly simple data and the my numbers aren't working out Some results are showing up <NULL> when I know the data is in the database ...
0
by: Rhino | last post by:
I am working with SQL Functions in DB2 for Windows/Linux/UNIX (V8.2.1) and am having a problem setting input parameters for SQL Functions to null in the Development Center. My simple function,...
1
by: PST | last post by:
Here's a problem I'm trying to deal with: I'm working on a Frontpage 2000 website for a boat handicapping system, built in Access 97. What I'm trying to accomplish is: The user enters a...
13
by: jt | last post by:
I can't seem to find a way to concatenate strings that have nulls within the string. I have a string that I need another string that has nulls in it and what to append the 2nd string, 3 string...
3
by: Simon | last post by:
Hi all, Do you think the best way to avoid the problems of nulls in the database is just to provide default values via the db schema? Alternatively, is it better to allow nulls, seeing as the...
2
by: Rey | last post by:
Howdy all. My problem deals w/inserting nulls into database (SQL Svr 2K) for the datetime fields activityDate and followUpDate where nulls are allowed. >From the web form, the user can type...
6
by: Cliff72 | last post by:
I need to fill in the nulls in the batch field the value from the record immediately preceding the null one ie replace the nulls with the preceding value until I hit a record with a value in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.