Posted by Paul on 02/07/07 09:20

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:

SQL> CREATE TABLE TEST
2 (COL1 VARCHAR(50));

Table created.

Elapsed: 00:00:00.07
SQL> INSERT INTO TEST (COL1) VALUES (NULL);

1 row created.

Elapsed: 00:00:00.00
SQL> INSERT INTO TEST (COL1) VALUES ('');

1 row created.

Elapsed: 00:00:00.00
SQL> SELECT 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

[Back to original message]