SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

24 October 2005

 

NULLs versus empty strings

SQL Server and Oracle treat columns containing strings with no length in very different ways.

Consider the following Microsoft SQL Server script:

CREATE TABLE nulltest ( a1 char (10) , a2 varchar (10) ) ON PRIMARY
go
insert into nulltest (a1,a2) values ('','')

go
select count(*)from nulltest where a1 is null

go
select count(*)from nulltest where a2 is null

go

The results from the two selects is 0 in both cases. SQL Server does not treat the empty strings as nulls.

The Oracle equivalent is:

CREATE TABLE nulltest ( a1 char (10) , a2 varchar2 (10) ) tablespace c1ts;
insert into nulltest (a1,a2) values ('','');
select count(*)from nulltest where a1 is null;
select count(*)from nulltest where a2 is null;


The two selects both return 1. Oracle is treating an empty string as a NULL.

Be wary when dealing with these cases.

Comments: Post a Comment

Subscribe to Post Comments [Atom]





<< Home

Links

Archives

September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]