Posted by Alex Kuznetsov on 02/07/07 15:08

On Feb 7, 3:20 am, "Paul" <paulwragg2...@hotmail.com> wrote:
> 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

There are more similar examples here:

www.devx.com/dbzone/Article/32852

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

[Back to original message]