Skip to content

” = empty string and/or NULL…

February 9, 2006

How many times have you seen '' used for a NULL string?

Oracle 9i SQL Reference Release 2 (9.2) – “Basic Elements of Oracle SQL, 5 of 10: Nulls”

“…Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.”

About these ads

From → SQL

4 Comments
  1. Gary permalink

    Going further back.

    “Oracle7 currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle7.”

  2. It’s hard to see how Oracle would go about making this change to Oracle now. Perhaps it’d be a matter of adding a new attribute for character columns that causes empty strings to be stored differently to NULLs. In addition, they’d have to make changes to the way that SQL and PL/SQL treat character strings. For convenience they might add a new system parameter that controls the default behaviour for newly-created tables.

    Of course, anyone wishing to take advantage of this change would have to carefully look at any existing code. If they’ve been setting strings to NULL instead of ” they’ll be fine. The most pernicious code will be dynamic SQL – how often have we seen this sort of code:

    EXECUTE IMMEDIATE ‘insert into mytable values (”’ || nullable_variable || ”’);';

Trackbacks & Pingbacks

  1. Empty strings are null in Oracle « Database Bulletin
  2. Top 10 Confusing Things in Oracle « Jeff Kemp on Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 196 other followers

%d bloggers like this: