HUH?  VARCHAR2?

You gotta love Oracle...ANSI be damned.  Well I never specifically knew why we needed to used varchar2 during the last several projects we were on.  Mostly because I was told to...don't think the person who directed the effort really knew either.

CHAR is simple, it retains any data you put in there AND space pads the values.  A pain for comparisons later on.

VARCHAR is not supported and is reserved for future use 

VARCHAR2's definition is a little more complicated since it's different between version (Hey, eve M$ tries to backward compatible)

Brian Peasland at http://searchoracle.techtarget.com puts it best

Quote:

Actually, Oracle Corp. has changed the semantics of the VARCHAR2 datatype. Currently, in Oracle 9i, when you insert a zero length string into a VARCHAR2 column, Oracle treats this the same as if you insert a NULL value into that column. But it wasn't always this way. Previous versions of the database treated zero length strings and NULL values as two distinctly different items.

And aren't they supposed to be different?  This is progress?  An empty string <> null, hell even null <> null..so what gives?  Is an empty string and null ANSI compliance?  Or does it have nothing to do with it?  Ah, more reasearch...

UPDATE:

Tony Andrews, a moderator over at dbforums points out the following...further adding to the confusion...nothing like testing things out...Thanks Tony

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Jan 5 17:27:58 2005

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> create table t (id int, text varchar2(10));

Table created.

SQL> insert into t values (1, '');

1 row created.

SQL> select * from t where text is null;

        ID TEXT
---------- ----------
         1

And then this

SQL> select * from t where text = '';

no rows selected

Go Figure!

Feel free to use these results in your blog!

Tony Andrews
http://tonyandrews.blogspot.com

Karen Morton's article at the site was also extremely helpful, and there are a lot of other good links

http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid506468_tax294551,00.html

And there's always my simplistic pleas for help

http://www.dbforums.com/showthread.php?p=4030309&posted=1#post4030309