NULLS and ''

[Paul]

Hi,

I don't have any specific details as I do not really understand why
this is happening but we have a customer database that we have been
using and queries on text fields have begun returning empty string
values instead of NULL.

So for example:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
ORDER BY 1

may have returned 50 records in the past (purely an example). It is
now returning an additional record first that appears just to be ''.
Now I am not too sure where to begin looking into this. ODBC
connections, collation settings? I just am not sure where to begin.
The column will not have had '' inserted into it; therefore it should
be NULL.

I have posted this mainly so I can see if anybody else has seen this
sort of behaviour - I cannot see why this has suddenly happened.
The collation setting on our server is different to that of the
customer DB, but this shouldn't make a difference should it?

If anybody has any ideas then I can post some more information.
Thanks,

Paul

[Steve]

On Feb 6, 3:04 am, "Paul" <[e-mail]:

Hi,

I don't have any specific details as I do not really understand why
this is happening but we have a customer database that we have been
using and queries on text fields have begun returning empty string
values instead of NULL.

So for example:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
ORDER BY 1

may have returned 50 records in the past (purely an example). It is
now returning an additional record first that appears just to be ''.
Now I am not too sure where to begin looking into this. ODBC
connections, collation settings? I just am not sure where to begin.
The column will not have had '' inserted into it; therefore it should
be NULL.

I have posted this mainly so I can see if anybody else has seen this
sort of behaviour - I cannot see why this has suddenly happened.
The collation setting on our server is different to that of the
customer DB, but this shouldn't make a difference should it?

If anybody has any ideas then I can post some more information.
Thanks,

Paul

Changes that could cause this include:
1. setting a default value of '' for a column
2. added a default value of '' to a sproc that inserted records
3. changing NULL to '' in the insert in the application

[Paul]

Hi Steve,

Unfortunately I have already covered all of these and determined that
it is not possible for this to have happened (unless the customer had
done this without our knowledge).

The thing is that they have not reported this as an issue, and there
are some areas of the system where it would show up for sure.

It seems only to have happened once we put their DB in to our
environment.

Thanks for the reply anyway!

Paul

[Plamen Ratchev]

Hi Paul,

Here is one more option to check. I have seen similar behavior on client
databases when somebody turned the CONCAT_NULL_YIELDS_NULL option to OFF.
Basically when it is OFF a string concatenation like SELECT TAB1.DESCRIPTION
+ NULL will return the string in TAB1.DESCRIPTION, not NULL. When
CONCAT_NULL_YIELDS_NULL is ON then SELECT TAB1.DESCRIPTION + NULL will
return NULL. I am not sure what is the logic and SQL of your application but
if you have string concatenation it could be it.

A good example of how it will affect returned records is if you have a query
like this:

SELECT FirstName + ' ' + MiddleInitial + ' ' + LastName AS EmployeeName
FROM Employees
WHERE FirstName + ' ' + MiddleInitial + ' ' + LastName IS NOT NULL

Then if you have MiddleInitial set to NULL for some records you will see
different results based on CONCAT_NULL_YIELDS_NULL being ON or OFF.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Paul]

Thanks Plamen, I am just about to go home for the evening so I will
check this out tommorrow.

I just wanted to post an update before I go home though.

I have been looking a bit more in to this and I have found that the
following seems even stranger to me.

If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0

always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?

If anybody can explain this then please do!

I will be looking in to Plamens suggestion tommorrow but in the
meantime if anybody has any other ideas it will be appreciated!

Thanks,

Paul

[Steve]

On Feb 6, 10:25 am, "Paul" <[e-mail]:

Thanks Plamen, I am just about to go home for the evening so I will
check this out tommorrow.

I just wanted to post an update before I go home though.

I have been looking a bit more in to this and I have found that the
following seems even stranger to me.

If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0

always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?

If anybody can explain this then please do!

I will be looking in to Plamens suggestion tommorrow but in the
meantime if anybody has any other ideas it will be appreciated!

Thanks,

Paul

An empty string has a DATALENGTH of 0 and is not NULL

[Plamen Ratchev]

Hi Paul,

There is a difference between NULL (or unknown value) and blank string (i.e.
''). If your column is NULL then DATALENGTH will return NULL, but if the
column is blank string then it will return 0.

As for your question if your query will always return zero records, that is
not correct. If you have a row where the DESCRIPTION column is a blank
string ('') then DATALENGTH will return 0 and since at the same time the
column is not NULL it will return it in the result set. Here is an example
to demonstrate this:

CREATE TABLE #Test (id int identity(1,1), description varchar(250) NULL)

INSERT INTO #Test (description) VALUES ('')
INSERT INTO #Test (description) VALUES (NULL)

SELECT id, description
FROM #Test
WHERE description IS NOT NULL
AND DATALENGTH(description) = 0

DROP TABLE #Test

The query above will return the first inserted row because it is not NULL
and the DATALENGTH of the blank string is 0.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Paul (pa***********@hotmail.com) writes:

If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0

always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?

No:

DECLARE @x varchar(234)
SELECT @x = ''
SELECT isnull = CASE WHEN @x IS NULL THEN 1 ELSE 0 END,
bytes = datalength(@x)

NULL and the empty string are two different values. Or maybe they the
same two values. After all, NULL is the unknown value, so you don't
know what it is. The empty string, on the other hamd, is a known value.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Paul]

Thanks to everybody for clarifying this for me. It still doesn't solve
my problem of how records that should be NULL are actually empty
strings - this should not have happened and I still cannot see why it
should have. Maybe it is our application after all. But if it was the
application then I would have hoped to see the same behaviour on
Oracle, but I do not, all the equivalent fields on Oracle are NULL
rather than ''.

If I do find anything I will post the results back here.

Thanks to everybody for their time,

Paul

[Paul]

Just to contradict my last post, I thought I would check this on
Oracle - I know this is a SQL Server newsgroup but I needed to check.
So if we have the following on SQL Server:

CREATE TABLE TEST
(COL1 VARCHAR(50))
GO

INSERT INTO TEST (COL1) VALUES (NULL)
GO

INSERT INTO TEST (COL1) VALUES ('')
GO

SELECT COUNT(1)
FROM TEST
WHERE COL1 IS NOT NULL;
GO

-----------
1

(1 row(s) affected)

So here, on SQL Server there is 1 row that is NOT NULL - the '' row.
Now doing the equivalent on Oracle:

SQLCREATE TABLE TEST
2 (COL1 VARCHAR(50));

Table created.

Elapsed: 00:00:00.07
SQLINSERT INTO TEST (COL1) VALUES (NULL);

1 row created.

Elapsed: 00:00:00.00
SQLINSERT INTO TEST (COL1) VALUES ('');

1 row created.

Elapsed: 00:00:00.00
SQLSELECT COUNT(1)
2 FROM TEST
3 WHERE COL1 IS NOT NULL;

COUNT(1)
----------
0

1 row selected.

So it appears that on SQL Server '' is treated as a known value i.e.
an empty string, whereas on Oracle '' is treated as NULL - maybe this
is because we do not specify anything between the quotes to insert.
Either way this is interesting, and could signify why we are now
seeing the problem on SQL Server but not on Oracle.

Paul

[Paul]

So, it is our application by the looks of things. We will now have to
implement a function so that instead of saving '' we save NULL.

Thanks for all those who helped with this!

Paul

[Alex Kuznetsov]

On Feb 7, 3:20 am, "Paul" <[e-mail]:

Just to contradict my last post, I thought I would check this on
Oracle - I know this is a SQL Server newsgroup but I needed to check.
So if we have the following on SQL Server:

CREATE TABLE TEST
(COL1 VARCHAR(50))
GO

INSERT INTO TEST (COL1) VALUES (NULL)
GO

INSERT INTO TEST (COL1) VALUES ('')
GO

SELECT COUNT(1)
FROM TEST
WHERE COL1 IS NOT NULL;
GO

-----------
1

(1 row(s) affected)

So here, on SQL Server there is 1 row that is NOT NULL - the '' row.
Now doing the equivalent on Oracle:

SQLCREATE TABLE TEST
2 (COL1 VARCHAR(50));

Table created.

Elapsed: 00:00:00.07
SQLINSERT INTO TEST (COL1) VALUES (NULL);

1 row created.

Elapsed: 00:00:00.00
SQLINSERT INTO TEST (COL1) VALUES ('');

1 row created.

Elapsed: 00:00:00.00
SQLSELECT COUNT(1)
2 FROM TEST
3 WHERE COL1 IS NOT NULL;

COUNT(1)
----------
0

1 row selected.

So it appears that on SQL Server '' is treated as a known value i.e.
an empty string, whereas on Oracle '' is treated as NULL - maybe this
is because we do not specify anything between the quotes to insert.
Either way this is interesting, and could signify why we are now
seeing the problem on SQL Server but not on Oracle.

Paul

There are more similar examples here:

www.devx.com/dbzone/Article/32852

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Paul]

Thanks for the link Alex - some very interesting points in there that
I am sure will be useful in the future.

Thanks again,

Paul