Oracle treats an empty string as NULL

23 Aug 2007 As a Microsoft SQL / MySQL developer I was surprised to find out today that Oracle doesn't support empty strings in "NOT NULL" fields! This is because Oracle treats an empty string, i.e. '', the same as NULL.

Of course this is old news to Oracle users, but even some of my colleagues who'd been using Oracle longer than me were unaware of this.

I realized this after trying to figure out why an INSERT statement like the following kept complaining that column B cannot be NULL:

insert into MYTABLE ( A, B ) values ( 'hello', '' )

I think that's not very clever at all.

That means, for example, if your code performs a search on a field, the following can never work:

select * from MYTABLE where B = :value

if the :value parameter is an empty string, since you'd have to say "where B is null" instead.

Google "oracle empty string null" for more descriptions.